For any number less than the value in “input”, the formula will return TRUE and the conditional formatting will be applied. However, if the value is not a number, the formula then checks if the first character is a less than symbol (<) using the LEFT function: If so, the MID function is used to extract everything after the symbol: Technically, the LEN function returns a number 1 greater than we need, since it includes the “<” symbol as well. If this bothers you, feel free to subtract 1. The result of MID is always text so the formula adds zero to force a Excel to convert the text to a number. This number is then compared to the value from “input”.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
