conditional formatting

[xceed]

Hello,
I am a not very experienced user of excel 2007.
I have a row of figures in a table, some cells may be blank. I total the highest 3. With help from past forum threads I have achieved this.
I now want to conditionally format these 3. Ideally I would prefer to format the non blank cells that are not included in the best 3.
The problem with using the standard selection of largest is that if the 4th highest is the same as the third, 4 are highlighted (1st row in attached).
Specifying bottom no. is worse due to empty cells (row 2).
My formula for totalling does not work when inserted into conditional formatting.
Some help much appreciated.

[DonkeyOte]

I now want to conditionally format these 3...
The problem with using the standard selection of largest is that if the 4th highest is the same as the third, 4 are highlighted (1st row in attached).

Highlight A2:I17 (selecting from A2 first)

Delete your existing rules

Choose to "Use a Formula" for your Conditional Format and insert the below:

Please Login or Register  to view this content.

Click OK etc...


Note: the use of the embedded INDEX in the above formula is pretty much a necessity if you're using XL2007 as implied (unless you wish to created Named Ranges) - this is not necessary in other versions
(in other words it is used to circumvent a bug in XL2007)

[xceed]

Thankyou Don Q. Beyond a mere mortal to do or understand it but I have successfully modified and put in to my spreadsheet. Thanks Again.

[zzmxy]

Highlight A2:I17 (selecting from A2 first)

Note: the use of the embedded INDEX in the above formula is pretty much a necessity if you're using XL2007 as implied (unless you wish to created Named Ranges) - this is not necessary in other versions
(in other words it is used to circumvent a bug in XL2007)

So glad to read this experience on xl07, and the data you generates in the memory is quite skillful, thanks again.
However, beside xl07,we can write the formula as below to simplify.

Please Login or Register  to view this content.