Trying to highlight lowest 3 values in a row, all with different colors

[ItisRue]

I am having issues finding a way to highlight the lowest three values using conditional formatting. I have done the top 3 values with mostly success, although, I have found that if there are two "#2" values, it doesn't highlight the 3rd, it just highlights the #1 and 2 #2's... Quite perplexing.

But, with regard to my question at hand, I am looking to highlight the 3 lowest scores on a list, all in a row. I have used "=SMALL(A3:A10, 3)", =SMALL(A3:A10, 2)", and "=SMALL(A3:A10, 1)" in hopes that this would work. All it returns to me is one color. Anyone have any advice?

Oh, and I am fairly new to this, so please bear with me if I am not using the correct terminology.

Thanks, in advance, for any help!

 

[Momentman]

Did you set up three different rules? each for the smallest, 2nd smallest and third 3rd smallest?

 

[bschwartz]

If you setup three different rules in Conditional Formatting with custom formulas each using one of those small formulas, that should work. Just make sure that each of the rules has a different color format.

 

[ItisRue]

Yes, I did set up three different rules. I am sure it is something very simple that I am missing, but this is my first time working with these.

 

[FDibbins]

Maybe this...
=<=SMALL(A3:A10, 3)

Remember, CF only works on TRUE or FALSE, so if the formula evaluates to TRUE, the rule will trigger. If it is not true, it will not trigger.

I have found that if there are two "#2" values, it doesn't highlight the 3rd

do you mean it wont color the 3rd-smallest value, or it colors both 2nd-place values? Both of these will happen if you have 2 identical values in the 1st or 2nd place. You need to include a tie-breaker

 

[ItisRue]

I tried using that and it just gave me an error. That has to be what I am searching for, somehow set up the value for the 3rd lowest and ask it if the value is lower then format the 2nd color, and then the 1st, but I am just not sure how to do it...

As for the second part, you are exactly right that it just colors the two matching values and ignores the third highest. How do you set up the tie breaker? Do I have to set up a fourth rule just in case there are dual values that match?

 

[ItisRue]

So, I have been playing around with this, and can get excel to do what I need it to do in a separate cell with the formula "SMALL=A3:A10, 3)" and so on. I even get the colors to work with formatting. But, when I try to insert the formula into the cells I NEED them in "A3-A10," it just colors the entire thing with one of the colors.

 

[bschwartz]

You need to return a TRUE or FALSE for conditional formatting. The formula should look something like this: =$A3=SMALL($A$3:$A$10,1)

You should highlight the whole range starting from A3 and use that formula as the new rule. I haven't tested it, but let me know how it goes.

 

[ItisRue]

You need to return a TRUE or FALSE for conditional formatting. The formula should look something like this: =$A3=SMALL($A$3:$A$10,1)

You should highlight the whole range starting from A3 and use that formula as the new rule. I haven't tested it, but let me know how it goes.

Thank you SO much! I knew I was missing something small and basic. I just tested it, and it looks like it works! Thank you thank you thank you!

 

[bschwartz]

You're very welcome. Thanks for the feedback.