Largest and second largest values in column, conditional formatting

[thaling]

Hi - I'm on 365 on a Mac, all updated.

I've checked several posts/threads about how to highlight the highest and second highest values in a column. Nothing works quite right for me using the following conditional format formulas:
b20=max($b20:$b1001)
b20=large($b20:$b1001,2)

The maximum value in the column is 40. I get proper formatting on all the 40's listed in the column, except in addition, the last 3 populated cells are formatted the same as 40 (rows 391-393) are also highlighted with the same conditional format, but those three cells don't contain the number 40 (they are all different single digit numbers).

The second highest value is 29. Only the last occurance of the number 29 is highlighted.

Thank you for your help

 

[StephenCrump]

Welcome to the Forum!

If 40 is the maximum, and there are more than one 40's, then LARGE( ... , 2) will also be 40.

Try: =B20=LARGE(UNIQUE($B20:$B1001),2)

If you've been trying different formulae, you may have unwanted conditional formatting still attached, and this may account for the strange results. You may want to clear all conditional formatting from the range and start again.

 

[thaling]

Welcome to the Forum!

If 40 is the maximum, and there are more than one 40's, then LARGE( ... , 2) will also be 40.

Try: =B20=LARGE(UNIQUE($B20:$B1001),2)

If you've been trying different formulae, you may have unwanted conditional formatting still attached, and this may account for the strange results. You may want to clear all conditional formatting from the range and start again.

 

[thaling]

Thanks so much for the fast reply and suggestion. I cleared all conditional formatting and tried the formula so the second largest number, 29, should have been highlighted, but nothing was highlighted. ????

 

[Fluff]

Can you post a sample of your data, include the conditional formatting.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.