Click here for important information about Computing.net.

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

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?

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

What if you have this? 1

2

3

3

4

5

6Is 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?

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.

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.

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.

Thank you for your efforts. I'm still trying to come up with something at this end.

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

Humar, with your formula, the results would look like this: 9

43551

6

72

9You'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.

I tried Humar's solution, and your right in that the duplicate is still highlighted. Still looking.

Duplicates are still highlighted Humar. Thanks for trying. I need only 5 scores to be highlighted.

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

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!

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 A2and A11 is this:

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

Humar

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.

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

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History