Format of a cell to change color based on formula ?

[Rob]

Using =SUM(LARGE(A1:A5, {1,2,3,4})) to get the top four scores and adding
them up in the list. How can I get the 4 items to change cell color?

Thanks Rob

scores
5.5
6.9
9.4
3.4
2.3

 

[Bob Greenblatt]

Using =SUM(LARGE(A1:A5, {1,2,3,4})) to get the top four scores and adding
them up in the list. How can I get the 4 items to change cell color?

Thanks Rob

scores
5.5
6.9
9.4
3.4
2.3

Select the range, then use Format-Conditional Formatting. Select Formula and
enter the following formula:
=A1>=LARGE($A$1:$A$5,4)

Then set the format to whatever you want. Click OK.

 

[Rob]

Thanks Bob
Works Great ,, Except blank cells are also changing to cell format choice.
Any Suggestion?

scores
5.5
6.9
9.4

3.4
2.3

 

[Bob Greenblatt]

Thanks Bob
Works Great ,, Except blank cells are also changing to cell format choice.
Any Suggestion?

scores
5.5
6.9
9.4

3.4
2.3

Shouldn't happen. Are you sure you have the formula correct? It works fine
for me.

 

[Rob]

Bob

I put in the following data below in the first 6 cells and cut aand pasted
=A1>=LARGE($A$1:$A$5,4) and 2,3,4,5,6 all change.

Thanks in advance
Rob

1
2
3
4
5
6

 

[Bob Greenblatt]

Bob

I put in the following data below in the first 6 cells and cut aand pasted
=A1>=LARGE($A$1:$A$5,4) and 2,3,4,5,6 all change.

Thanks in advance
Rob

1
2
3
4
5
6

OK, so you have 6 values, 1=6 in A1:A6, and the formula in the conditional
formula for all 6 cells right? If so, the formula refers to A1:A5, so it is
looking at the top 4 values from a1:a5, but setting 6 cells. Change the
range to cover the values you are comparing and it will work as you expect
it to.

 

[Rob]

Bob
Thanks for the quick response. However if I had the following
Data
2
2
2
4
5
I still get all cells changing according to conditional format
statement is =A1>=LARGE($A$1:$A$5,4).

However when I use the following :
{=SUM(LARGE(C5:C28,ROW(1:4)))} it does add up correctly however cannot use
this in the conditional format.

Thanks again Rob

 

[Bob Greenblatt]

Bob
Thanks for the quick response. However if I had the following
Data
2
2
2
4
5
I still get all cells changing according to conditional format
statement is =A1>=LARGE($A$1:$A$5,4).

However when I use the following :
{=SUM(LARGE(C5:C28,ROW(1:4)))} it does add up correctly however cannot use
this in the conditional format.

Thanks again Rob

This seems correct to me. The 4 largest values are 5,4,2. The formula says
that if the cell is greater or equal to the 4th largest (2 in this case)
then change the format. So it is CORRECT for all 5 cells in the above
example to have the changed format. What are you expecting?

 

[Rob]

Bob

Go it. Works Great,, thanks for your help and time...

thank you thank you
Rob