Conditional formatting not highlighting highest distinct values

Hi,

I hope someone can help me! I have a list of values in which I need all cells that have the 3 highest values highlighted in a certain colour. Conditional formatting will highlight the top 3 for example, but it doesnt allow for the distinct values to be highlighted, only the top 3 values themselves.

ie: I have a list as follows:
60
30
50
30
30
50
40
40
30
30

So I would all cells that have a 60, a 50 or a 40 highlighted. If you use the conditional formatting help it would only highlight the 60, the 50 and the other 50.

Can someone give me a hand?

Thanks

Steve

HI and thanks for the welcome.

The actual sheet has sensitive information on so I cannot attach it - and I am using Excel 2013 as far as I can tell. Sorry to be vague but this is doing my head in!

Many thanks in advance for any assistance.

Steve.

OK I think I have attached a sample sheet now

How about
=A1>=LARGE(IF(ISNUMBER($A$1:$A$20),IF(ROW($A$1:$A$20)=MATCH($A$1:$A$20,$A$1:$A$20,0),$A$1:$A$20)),3)

Oh Wow! That is perfect!

Thank you so much for your help.

Steve

You're welcome & thanks for the feedback.

Sorry to raise this query again - I tried to drop that formula into the actual spreadsheet I am working on and it doesnt seem to work? I cannot work out why. I have attached another test sheet with the amounts in that I need formatted. Basically I enter values into cells P6:P25 and they copy down divided by 10 into cells F31:F50. It is these cells in F31:F50 that I need formatted - can you help again?

Done!

Many thanks

How about
=F31>=LARGE(IF(ISNUMBER($F$31:$F$50),IF(ROW($F$31:$F$50)-ROW($F$31)+1=MATCH($F$31:$F$50,$F$31:$F$50,0),$F$31:$F$50)),3)

Superb again Fluff! I tried to cut and paste your initial formula and change the cell references but couldnt get it to work - but this seems to work a treat. Thanks once again!

You're welcome & thanks for the feedback.

I wonder if I can tap into your brain again Fluff? I have used the formulas you have mentioned into my sheet and it works perfectly. However, there is one cell that needs to have a value depending on all 4 certain cells being of a certain value. For example, in sheet TESTING SHEET #2 the cells from J31 down to J50 need to have a value of 'YES' if the 4 cells to the left or it are highlighted. I think this can be done in VB but I tried to drop your formula in and tweak it and - you guessed it - I couldn't do it.

Ideally the formula should say that, for cell J31 for example, that if F31 is in the top 3 unique values between F31:F50 AND G31 is in the top 3 values between G31:G50 AND H31 is in the lowest 3 values between H31:H50 AND I31 is more than 4 then the cell should say 'YES'. This needs to work for cells from J31 to J50 if you know what I mean.

Does that make sense? Are you OK to help me out again? This will be the last time. I promise.

You will need to use the same formulae that you us in the CF.

Hi Fluff. Yes I did try that but it doesn't seem to work. I can get it working for cell J31 bit when you copy the formula down it stops working correctly - even if I update the cell references. I thought it would be quite simple but there is obviously something I am not doing right.

Try
=IF(AND(F31>=LARGE(IF(ISNUMBER($F$31:$F$50),IF(ROW($F$31:$F$50)-ROW($F$31)+1=MATCH($F$31:$F$50,$F$31:$F$50,0),$F$31:$F$50)),3),G31>=LARGE($G$31:$G$50,3),H31<=SMALL($H$31:$H$50,3)),"Yes","")

It doesnt quite work right I am afraid. Do not worry though, you have already spent enough time on this Fluff. I really do appreciate the help you have given me so far though.

It works for me

Wow! Yes that's great. I will use that. Really not sure what I am doing that doesn't make it work but thank you so so much for looking at this for me. I feel like I owe you something??

You're welcome & thanks for the feedback.