A recent Quora question asked “How do I highlight the number closest to an initial number in a row in Excel?“. The answer by Bill Jelen provides a good example of using conditional formatting with an array formula:
In my data, I have the Goal or Initial Number in column A. Then, there are 12 results in B:M of each row. Select B3:M16. Home, Conditional Formatting, New Rule, Use a Formula To Determine Which Cells To Format. The formula will be =ABS($A3-B3)=MIN(ABS($A3-$B3:$M3)). Click the Format button and choose your desired fill color or font color.
…Note the single dollar sign before the $A, $A, $B, and $M are crucial to make this work. As Conditional Formatting evaluates each cell, those dollar signs make sure that the logic is always pointing at A for the Initial cell and the range of B:M for the other values which may or may not be closer.
Note: This was created using Office 365 Insider which has access to Dynamic Arrays.
When used for conditional formatting, this formula also works with non-Insider versions of Office 365, as can be seen in my example below:
Newton Excel Bach, not (just) an Excel Blog 