Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Cell in custom format???? | Excel Discussion (Misc queries) | |||
Why does my format keep changing in the cell? | Excel Discussion (Misc queries) | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Worksheet Functions | |||
How do I unhide the cell format function in Excel 2000 | Excel Worksheet Functions |