Forum Discussion
Conditional Formatting
Okay I need some help. I've watched just about every video and read every article I can find and nothing is working or answering my question directly and frankly I'm not good enough with excel to figure it out. So here is what I'm trying to do. I have a spread sheet with currency values in a row separated by percentage. In other words the currency values are non contiguous. I'm trying to conditionally format the bottom 3 values with separate color cells and font e.g. green, orange, & red. The cells are as such C14, E14, G14, I14, K14, M14, .... ect. The Small formula won't work because its using and array of non contiguous cells. I have found multiple ways to highlight the smaller cell, or the smallest 3, but not a formula that will allow me to apply a separate conditional format to each of the smallest three. Any help would be appreciated.
4 Replies
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.
