Explanation
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:
$C3<=SMALL(IF(color=$E$5,amount),3)
Here, we filter amounts to make sure that only values associated with the color in E5 (blue) are retained. The filtering is done with the IF function like this:
IF(color=$E$5,amount)
The resulting array looks like this:
{FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500}
Notice the value from the amount column only survives if the color is “blue”. Other amounts are now FALSE.
Next, this array goes into the SMALL function with a k value of 3, and SMALL returns the “3rd smallest” value, 300. The logic for the second logical test reduces to:
$C3<=300
When both logical conditions are return TRUE, the conditional formatting is triggered and cells are highlighted.
Note: this is an array formula, but does not require control + shift + enter.
Explanation
Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. However, by using your own formula, you have more flexibility and control.
In this example, a conditional formatting rule is set up to highlight cells in the range C5:G15 when then are greater than the value entered in cell J6. The formula used to create the rule is:
=C5>=$J$6
The rule is applied to the entire range C5:G15, and the value in J6 can be changed at any time by the user. When a new value is entered, the highlighting is immediately updated.
The formula uses the greater than or equal to operator (>=) to evaluate each cell in the range against the value in J6. The reference to C5 is relative and changes as the formula is evaluated for each cell in the range. The reference to cell J6 is “locked” as an absolute reference ($J$6).
When a value in the range is greater than or equal to 15 (the current value in J6), the formula returns TRUE and the rule is triggered.
