Conditional format row based on percentile

A

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?
 
S

Spiky

Sounds like you have v2007, since I don't recognize all those
features. But you should be able to select all of the cells you want
formatted in one row (so A2:N2 or similar), then do your conditional
format, using absolute references to Column K. Then you should be able
to copy the formats down the rows without trouble.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top