Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format row based on Percentile in one cell?
I asked a similar question the other day and no one answered, because I
posted it in general questions instead of worksheet functions. So I'm trying again. I have 2 similar worksheets with 15 columns and 100 rows. Values in Column K have a conditional format that highlights the cell in green if it is one of the top 10 values in that column (on another worksheet it's if in top 20%). I want the entire row to be the same format color, based on Column K. I can't just copy the format because some of the columns are in "," format, some in "%" format, and so on. I'm not proficient enough in Excel to know how to write this formula as a conditional format. Here is an abbreviated picture of what rows 1 and 9 look like. To get the conditional format in Column D I chose "Conditional formatting - highlighting rules - "Format only top or bottom ranked values" - "format values that rank in the [drop down menu] top" 10 [fill in] as green fill. In the example below, K2 is not in the top 10 values, but K9 is. On my spreadsheet cell K9 is green. I would like to make all the cells in that row be green. I can't copy the format, because some of the columns are percentages, some are dollars, and so on. I entered the formula by using drop down menus in the conditional formatting choices, and I don't know how to put a formula in the other columns that says if $K9 is in Top 10 of $K$2:$K$100, then green fill, otherwise no fill. A B C €¦€¦ K M N 1 Name YTD DT % DT Goal Hrly Rate Adj DT % New Target 2 Bowen 70.6% 85% $36.94 + 5% $66,153 9 Clark 81.0% 82% $56.48 +1% $98,722 Can anyone offer a solution? -- Thank you. A Canes Fan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format row based on Percentile in one cell?
Hi,
You didn't say if this was <2007 or 2007? Highlight the entire range where you want the formatting, choose Format, Conditional Formatting, Formula is, =$K2=whatever, then choose the format you want to apply. the Whatever is the formula you are using currently for the top 10 or top 20%. It sounds like you have the Whatever part already figured out but if not then for the top 20%: $K2=SUM($K$2:$K$100)*.2 -- Cheers, Shane Devenshire "Acanesfan" wrote: I asked a similar question the other day and no one answered, because I posted it in general questions instead of worksheet functions. So I'm trying again. I have 2 similar worksheets with 15 columns and 100 rows. Values in Column K have a conditional format that highlights the cell in green if it is one of the top 10 values in that column (on another worksheet it's if in top 20%). I want the entire row to be the same format color, based on Column K. I can't just copy the format because some of the columns are in "," format, some in "%" format, and so on. I'm not proficient enough in Excel to know how to write this formula as a conditional format. Here is an abbreviated picture of what rows 1 and 9 look like. To get the conditional format in Column D I chose "Conditional formatting - highlighting rules - "Format only top or bottom ranked values" - "format values that rank in the [drop down menu] top" 10 [fill in] as green fill. In the example below, K2 is not in the top 10 values, but K9 is. On my spreadsheet cell K9 is green. I would like to make all the cells in that row be green. I can't copy the format, because some of the columns are percentages, some are dollars, and so on. I entered the formula by using drop down menus in the conditional formatting choices, and I don't know how to put a formula in the other columns that says if $K9 is in Top 10 of $K$2:$K$100, then green fill, otherwise no fill. A B C €¦€¦ K M N 1 Name YTD DT % DT Goal Hrly Rate Adj DT % New Target 2 Bowen 70.6% 85% $36.94 + 5% $66,153 9 Clark 81.0% 82% $56.48 +1% $98,722 Can anyone offer a solution? -- Thank you. A Canes Fan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format row based on Percentile in one cell?
Excel 2007. I tried the formula, but I couldn't get it to work, so I broke
it into pieces. If I understand the "whatever" part of your criteria, it is telling me to add up the total of column K then multiply that by 20%. Suppose the total of all of the hourly salaries in column K = $1,000. Multiply that by .2 and you get $200. No one is making $200 per hour, so nothing is getting highlighted. In my original example, I would want row 9 to be highlighted, because that person is one of the highest paid. What am I doing wrong? -- Thank you. A Canes Fan "ShaneDevenshire" wrote: Hi, You didn't say if this was <2007 or 2007? Highlight the entire range where you want the formatting, choose Format, Conditional Formatting, Formula is, =$K2=whatever, then choose the format you want to apply. the Whatever is the formula you are using currently for the top 10 or top 20%. It sounds like you have the Whatever part already figured out but if not then for the top 20%: $K2=SUM($K$2:$K$100)*.2 -- Cheers, Shane Devenshire "Acanesfan" wrote: I asked a similar question the other day and no one answered, because I posted it in general questions instead of worksheet functions. So I'm trying again. I have 2 similar worksheets with 15 columns and 100 rows. Values in Column K have a conditional format that highlights the cell in green if it is one of the top 10 values in that column (on another worksheet it's if in top 20%). I want the entire row to be the same format color, based on Column K. I can't just copy the format because some of the columns are in "," format, some in "%" format, and so on. I'm not proficient enough in Excel to know how to write this formula as a conditional format. Here is an abbreviated picture of what rows 1 and 9 look like. To get the conditional format in Column D I chose "Conditional formatting - highlighting rules - "Format only top or bottom ranked values" - "format values that rank in the [drop down menu] top" 10 [fill in] as green fill. In the example below, K2 is not in the top 10 values, but K9 is. On my spreadsheet cell K9 is green. I would like to make all the cells in that row be green. I can't copy the format, because some of the columns are percentages, some are dollars, and so on. I entered the formula by using drop down menus in the conditional formatting choices, and I don't know how to put a formula in the other columns that says if $K9 is in Top 10 of $K$2:$K$100, then green fill, otherwise no fill. A B C €¦€¦ K M N 1 Name YTD DT % DT Goal Hrly Rate Adj DT % New Target 2 Bowen 70.6% 85% $36.94 + 5% $66,153 9 Clark 81.0% 82% $56.48 +1% $98,722 Can anyone offer a solution? -- Thank you. A Canes Fan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format on column based on cell value | Excel Worksheet Functions | |||
Conditional Format based on value of another cell | Excel Worksheet Functions | |||
Conditional Format a row based on a cell? | Excel Worksheet Functions | |||
Conditional Format based on other cell | Excel Discussion (Misc queries) | |||
Conditional Format based on other cell | Excel Discussion (Misc queries) |