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,
Hopefully this is the last post by me. I was able to adjust the codes arrays to fit my spread sheet and make it work. Sorry for the back and forth, but thank you again for your help. I guess I need to work on it a bit before I ask for help.
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?
Sergei,
For some reason when I copy your formula from the text it doesn't work. So I downloaded the file you attached and it works just fine. Not sure what's going on there buy you are a serious time saver. Thank you so much for your help.
