Conditional format row based on Percentile in one cell?

[Acanesfan]

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?

 

[ShaneDevenshire]

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

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?

 

[Acanesfan]

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

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

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?