highlight 5 lowest values in Excel

August 27, 2010 at 09:12:53
Specs: Windows 7
i need to highlight 5 lowest values in a column without highlighting the 6th value if it is the same as the 5th lowest value in excel

See More: highlight 5 lowest values in Excel

Report •


#1
August 27, 2010 at 09:40:51
How will you decide which is the 5th and which is the 6th if they are equal?

Will it be based on the first "5th" value found e.g. the value in A25, not the value in A27?

Also, what version of Excel or you using?


Report •

#2
August 27, 2010 at 09:47:08
Excel 2007 is what I'm using. I just need the 5 lowest numbers. It doesn't matter which one is selected to be ignored.Thanks for your reply

Report •

#3
August 27, 2010 at 11:32:20
What if you have this?

1
2
3
3
4
5
6

Is the answer 1 2 3 3 4?

In other words, are duplicates allowed within the 5 lowest values?

I've played around with Conditional Formatting and couldn't get anything to work. Is a VBA (macro) solution acceptable?


Report •

Related Solutions

#4
August 27, 2010 at 11:55:05
Yes duplicates are allowed. It's to calculate scores for a golf league. We drop your 5 worst scores out of 10 scores posted. I already have a solution to do that but would like to highlight the lowest scores used in that calculation.

Report •

#5
August 27, 2010 at 11:57:21
Would like to avoid using a macro. I've been trying different things with Conditional Formatting with no success. Am hoping that I could put a formula in Conditional Formatting.

Report •

#6
August 27, 2010 at 12:22:14
You can certainly put a formula in Conditional Formatting, but I haven't (yet) found one that does what you want.

I tried a few things with SMALL and VLOOKUP but either got too many values or too few.

Knowing that you have 10 numbers and that dups are allowed, I'll play around some more.


Report •

#7
August 27, 2010 at 12:30:55
Thank you for your efforts. I'm still trying to come up with something at this end.

Report •

#8
August 28, 2010 at 05:30:46
Hi,

If you have ten scores in cells A2 to A11,
Apply this conditional formatting formula to cell A2:

=IF(AND(A2>SMALL($A$2:$A$11,1), A2>SMALL($A$2:$A$11,2), A2>SMALL($A$2:$A$11,3),
A2>SMALL($A$2:$A$11,4), A2>SMALL($A$2:$A$11,5)), FALSE,TRUE)

Note that the formula has been split onto two lines for ease of viewing.
Select a cell format.
Apply the conditional format formula to all ten cells.

Now the cells with the five lowest scores will be highlighted.

Is this what you were looking for.

Regards


Report •

#9
August 28, 2010 at 06:21:35
Humar, with your formula, the results would look like this:

9
4
3
5
5
1
6
7
2
9

You'll note the both 5's are highlighted. The OP only wants one of those to be highlighted.

There should never be more that 5 scores highlighted.


Report •

#10
August 28, 2010 at 07:56:43
I tried Humar's solution, and your right in that the duplicate is still highlighted. Still looking.

Report •

#11
August 28, 2010 at 07:58:31
Duplicates are still highlighted Humar. Thanks for trying. I need only 5 scores to be highlighted.

Report •

#12
August 28, 2010 at 10:39:55
Hi,

Try this one:

=IF(COUNTIF($A$2:$A$11,"<"&A2)>=5,FALSE,IF(COUNTIF($A$2:$A$11,"<"&A2)+COUNTIF($A$1:A1,"="&A2)>=5,FALSE,TRUE))

(This is the formula in cell A2, the first cell with a score)
It does require one empty cell above the first score, or a cell without a number in it.

In cell A11 this is what it looks like:

=IF(COUNTIF($A$2:$A$11,"<"&A11)>=5,FALSE,IF(COUNTIF($A$2:$A$11,"<"&A11)+COUNTIF($A$1:A10,"="&A11)>=5,FALSE,TRUE))

Regards


Report •

#13
August 28, 2010 at 11:09:36
Seems to work great Humar. Thank you very much for the solution to my problem. I would have never been able to figure that out on my own. Thanks again!

Report •

#14
August 28, 2010 at 13:55:39
Hi,

Glad to have been able to help.

It was an interesting problem.

Looking at it again, I think that you only need this:

=IF(COUNTIF($A$2:$A$11,"<"&A2)+COUNTIF($A$1:A1,"="&A2)>=5,FALSE,TRUE)
- in A2

and A11 is this:

=IF(COUNTIF($A$2:$A$11,"<"&A11)+COUNTIF($A$1:A10,"="&A11)>=5,FALSE,TRUE)

Regards

Humar


Report •

#15
August 28, 2010 at 14:36:15
Hi Humar,
Thanks again. To tell you the truth, I only put the first string you gave me in Conditional Formatting formula for the entire range of cells and it works perfectly. I'll keep testing it but so far I haven't had to use that second line.

Report •

#16
August 28, 2010 at 15:33:24
Hi,

The second line was just to show how the conditional formatting formula changes from cell to cell down the range.

Excel should automatically adjust it when you apply it to a range.

My last post was just a shortened version of the formula - you could use it ...
but as the one you have is working OK, you probably just want to stay with it.

Regards

Humar


Report •


Ask Question