Forum Discussion
Conditional Formatting
Hi Benjamin,
Assuming your figures are in every odd column you may find, for example, third smallest as
=AGGREGATE(15,6,1/ISODD(COLUMN($C$14:$M$14))*$C$14:$M$14,3)
to highlight third smallest in your range you may apply conditional formatting rule
=C14=AGGREGATE(15,6,1/ISODD(COLUMN($C14:$M14))*$C14:$M14,3))*ISODD(COLUMN(C14))
and similar rules for first and second smallest. Result will be as
and in attached
Sergei,
I've noticed one little problem. Some of the cells have an error value because the store did not sell that type of product. Let me see if I can be more clear. In my sheet I have a store, the cost of an item, there markup and the difference. To the right of the difference is the percentage hence the non contiguous cells. So the formula that you created was to highlight the three stores with the smallest markup on their product while ignoring the percentage of the markup to the right of each value. Some of the stores did not sell the product in which case there markup value returns as #VALUE!. In your formula when there are stores included that did not have value it throws it off and is not returning the 3 smallest markups. Is there a quick way around this?