Conditional Formatting Question

[The Shadowman]

Hi all

I have one row of five columns. Each cell of the five will have a number placed in it, and I want the highest three numbers of the five to have a red background.

Example would be A1, B1, C1, D1, E1, each cell could have say, 25, 30, 36, 19, 21. In this scenario I would want C1, B1 and A1 to have a red background.

My problem is: What formula do I place in the Conditional Formatting Box. If the numbers change, I want the red background to always be with the three highest.

If anybody can attempt to explain I would be very grateful.

Excel 2000

Thanks

Robert

[CK76]

I was going to suggest...
=A1>=LARGE($A$1:$E$1,3)

But then I realized LARGE function isn't available for Excel 2000...

Sorry, I'm only familiar with 2003 & up.

[davsth]

likewise with large but i believe median exists in 2000 and the median in this instance will be the 3rd highest value

=A1>=median($A$1:$E$1)

hopefully will work

[The Shadowman]

Thanks for your help, but I haven't been specific enough. I thought the example I gave could be adjusted, but I was wrong and still cant do it.

The actual cells I want to conditionally format are: D26, F26, H26, J26, L26. I had some success with the Median formula, but the cells in between those given above went red, the ones with my numbers in stayed white.

Also what item do I select where it says "between" "not between" "equals or greater than" etc. etc.

Thanks again

Robert

[CK76]

Then I suppose you need to apply CF to each cell separately (or check against Column Index).

For D26:
=D$26>=MEDIAN(D$26,F$26,H$26,J$26,L$26)

Copy to each of other cells.

[The Shadowman]

Excuse my ignorance, but what is CF. Sorry, I know what CF is, Ive been trying this for so long now I'm half asleep. What do you mean by check column index?

The problem I seem to have now is I put your formula in the cell, then I cant enter a figure. Im getting a bit lost to be honest

[sandy666]

Both function: LARGE() and MEDIAN() exist in Ex'97 so they are exist in Ex2000 for sure

largemedian.jpg

[The Shadowman]

Attachment 559390 This image help

[sandy666]

Invalid Attachment specified.

[The Shadowman]

That's odd it opens for me.

I think I will have to give up. One other question though. The first formula ie LARGE in the second post seems to work, But is the formula in each of the cells the same or do I need to adjust for each one