format cell with maximum value
 

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Use conditional formatting. If your data starts in A7, then in A7, Format
Conditional Formatting. Use the drop-downs and text box to create your
condition:
If cell value is equal to =MAX($A7:$J7), then set your format appropriately.
Copy the format through your entire table.

"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Say your data is in A1:J10. Select the range so that A1 is the
activecell. It should be white and you should see A1 in the Name Box on
the Forula Bar. Then from the menus select FormatConditional
Formatting. Change the first drop down from "Cell Value Is" to "Formula
Is" and in the second box enter the formula:
=A1=MAX($A1:$J1)
Click on the format button and choose your required format.

Note if your data is in a different range you will need to adjust the
formula accordingly.

Hope this helps
Rowan

Frank Drost wrote:
I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Thanks for the response, but it does not what I want it to do. I have at the
moment
for a cell (B2) the following:

Cell value is|equal to|=max($B2:$K2)

format| colour cell red

the row has the following numbers, starting with b2

0 0 0 2 0 0 4 0 0 0

What I want is that if I place that conditional formatting on all those
cells, only cell H2 (value = 4) will turn red. What else do I need to do in
my formatting to achieve that? And I need to do that for all my rows.

ta


"bpeltzer" wrote:

Use conditional formatting. If your data starts in A7, then in A7, Format
Conditional Formatting. Use the drop-downs and text box to create your
condition:
If cell value is equal to =MAX($A7:$J7), then set your format appropriately.
Copy the format through your entire table.

"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Hopefully you checked that if B2 is the max in the row that you indeed get
the format desired. Now you just need to apply the conditional format (CF)
to your entire table. So either select the entire table and apply the CF.
Or select the cell to which you've applied the CF and copy it, then select
your entire table and Edit Paste Special, select the Formats radio button
and click OK.


"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Great! That worked. I missed the trick of paste special with the format radio
button.
But just to make it slightly more complicated, what if I want to have the
same criterium, but with an extra statement in it that the value in column 10
(K) should be less than e.g. 50? So, the cell will only be coloured red IF it
is the highest number of the row and IF the value in column k is less than 50

Thanks


"bpeltzer" wrote:

Hopefully you checked that if B2 is the max in the row that you indeed get
the format desired. Now you just need to apply the conditional format (CF)
to your entire table. So either select the entire table and apply the CF.
Or select the cell to which you've applied the CF and copy it, then select
your entire table and Edit Paste Special, select the Formats radio button
and click OK.


"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Change the CF condition to read: cell value is equal to
=IF($k7<50,MAX($A7:$J7),A7-1). (That's the condition for cell A7, assuming
the range considered for the max is in columns A:J and the threshold value is
in column K; as before copy the CF to your entire table.) The logic in the
equation is: if the value in column K is less than 50, format the cell if it
is the row's max. If the value in column K is =50, format the cell if it is
equal to one less than the cell (that is, never!). --Bruce

"Frank Drost" wrote:

Great! That worked. I missed the trick of paste special with the format radio
button.
But just to make it slightly more complicated, what if I want to have the
same criterium, but with an extra statement in it that the value in column 10
(K) should be less than e.g. 50? So, the cell will only be coloured red IF it
is the highest number of the row and IF the value in column k is less than 50

Thanks


"bpeltzer" wrote:

Hopefully you checked that if B2 is the max in the row that you indeed get
the format desired. Now you just need to apply the conditional format (CF)
to your entire table. So either select the entire table and apply the CF.
Or select the cell to which you've applied the CF and copy it, then select
your entire table and Edit Paste Special, select the Formats radio button
and click OK.


"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

That is brilliant! Thanks a lot. It makes my table look much easier. The real
dataset is well over 30000 lines.
Maybe I am pushing it a bit too much, but is it also possible to have an
extra statement in the CF that checks that if the value in a cell is the
second highest value in that row, under the condition that column k is less
than 50, gets a colour green. And then for the third highest value, under the
same conditions, the colour yellow.
I see that under CF I can click on the button "add" and that gives me too
more CF possibilities. Can they be combined? And how do I find the second and
third highest value in the row?

This might be not possible in EXCEL, but hey, I have learned some new tricks
now, so who knows this might be possible after all.

ta


"bpeltzer" wrote:

Change the CF condition to read: cell value is equal to
=IF($k7<50,MAX($A7:$J7),A7-1). (That's the condition for cell A7, assuming
the range considered for the max is in columns A:J and the threshold value is
in column K; as before copy the CF to your entire table.) The logic in the
equation is: if the value in column K is less than 50, format the cell if it
is the row's max. If the value in column K is =50, format the cell if it is
equal to one less than the cell (that is, never!). --Bruce

"Frank Drost" wrote:

Great! That worked. I missed the trick of paste special with the format radio
button.
But just to make it slightly more complicated, what if I want to have the
same criterium, but with an extra statement in it that the value in column 10
(K) should be less than e.g. 50? So, the cell will only be coloured red IF it
is the highest number of the row and IF the value in column k is less than 50

Thanks


"bpeltzer" wrote:

Hopefully you checked that if B2 is the max in the row that you indeed get
the format desired. Now you just need to apply the conditional format (CF)
to your entire table. So either select the entire table and apply the CF.
Or select the cell to which you've applied the CF and copy it, then select
your entire table and Edit Paste Special, select the Formats radio button
and click OK.


"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks

format cell with maximum value
 

Multiple conditions (up to 3) CAN be combined. The first condition that is
satisfied gets applied.
As for the 2nd and 3rd largest, replace MAX(range) with LARGE(range,2) and
LARGE(range,3).


"Frank Drost" wrote:

That is brilliant! Thanks a lot. It makes my table look much easier. The real
dataset is well over 30000 lines.
Maybe I am pushing it a bit too much, but is it also possible to have an
extra statement in the CF that checks that if the value in a cell is the
second highest value in that row, under the condition that column k is less
than 50, gets a colour green. And then for the third highest value, under the
same conditions, the colour yellow.
I see that under CF I can click on the button "add" and that gives me too
more CF possibilities. Can they be combined? And how do I find the second and
third highest value in the row?

This might be not possible in EXCEL, but hey, I have learned some new tricks
now, so who knows this might be possible after all.

ta


"bpeltzer" wrote:

Change the CF condition to read: cell value is equal to
=IF($k7<50,MAX($A7:$J7),A7-1). (That's the condition for cell A7, assuming
the range considered for the max is in columns A:J and the threshold value is
in column K; as before copy the CF to your entire table.) The logic in the
equation is: if the value in column K is less than 50, format the cell if it
is the row's max. If the value in column K is =50, format the cell if it is
equal to one less than the cell (that is, never!). --Bruce

"Frank Drost" wrote:

Great! That worked. I missed the trick of paste special with the format radio
button.
But just to make it slightly more complicated, what if I want to have the
same criterium, but with an extra statement in it that the value in column 10
(K) should be less than e.g. 50? So, the cell will only be coloured red IF it
is the highest number of the row and IF the value in column k is less than 50

Thanks


"bpeltzer" wrote:

Hopefully you checked that if B2 is the max in the row that you indeed get
the format desired. Now you just need to apply the conditional format (CF)
to your entire table. So either select the entire table and apply the CF.
Or select the cell to which you've applied the CF and copy it, then select
your entire table and Edit Paste Special, select the Formats radio button
and click OK.


"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks