Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Frank Drost
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Rowan Drummond
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Frank Drost
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Frank Drost
 
Posts: n/a
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Frank Drost
 
Posts: n/a
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.misc
bpeltzer
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reference Cell in custom format???? lil_ern63 Excel Discussion (Misc queries) 3 September 1st 05 02:27 PM
Why does my format keep changing in the cell? Denise Excel Discussion (Misc queries) 0 August 30th 05 04:23 PM
how do I format a cell reference to move as source changes KGray Excel Worksheet Functions 1 August 13th 05 12:41 AM
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? Markus Obermayer Excel Worksheet Functions 1 January 4th 05 09:01 PM
How do I unhide the cell format function in Excel 2000 Len Melcer Excel Worksheet Functions 2 December 15th 04 07:49 PM


All times are GMT +1. The time now is 09:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"