Formula in Conditional Format doesn't seem to work

Posted by Ryan on August 07, 2001 7:08 AM

I have a data set with percentages (the data set is $Z$3:$Z$81). I'm trying to apply a conditional format that will highlight the largest 5 values. I've tried the following formula in the conditional format:

=OR(Z3=LARGE($Z$3:$Z$81, 1), Z3=LARGE($Z$3:$Z$81, 2), Z3=LARGE($Z$3:$Z$81, 3), Z3=LARGE($Z$3:$Z$81, 4), Z3=LARGE($Z$3:$Z$81, 5))

Additionally, the conditional format I'm trying to apply is to outline the box and shade the background grey. When I copy this format down to the other cells, no conditional formats are applied to any of the cells. I'm probable making a dumb error, but any assistance would be apprecatied. Thanks!!

Posted by Aladin Akyurek on August 07, 2001 7:47 AM

Ryan,

Activate Z3.
Activate the option Format|Conditional Formatting.
Select Formula Is.
Enter your formula or the shorter version

=AND($Z3<=LARGE($Z$3:$Z$81, 1), $Z3 >= LARGE($Z$3:$Z$81, 5))

Activate Format.
Select the border you want on the Border tab.
Select grey on the Patterns tab.
Click OK.

While in Z3, click the icon Format Painter, then select the range Z4:Z81 and let it go.

Note. This will give you at least 5 formatted cells in a range of 5 or more cells with numbers. Otherwise, you need to use RANK to get exactly 5 formatted cells.

Aladin

==========

Posted by Ryan on August 07, 2001 8:17 AM

Aladin,

Thanks for the reply. I followed your steps exactly, yet no conditional formats were applied. I then realized I had a #VALUE in the dataset, which was prohibiting the conditional format.

Thanks,
Ryan

Posted by Aladin Akyurek on August 07, 2001 8:26 AM

Thanks for the reply. I followed your steps exactly, yet no conditional formats were applied. I then realized I had a #VALUE in the dataset, which was prohibiting the conditional format.

You have apparently a formula which is applied to the range Z3:Z81.

Care to post that formula in order to see why it produces that error?

Aladin

Posted by Scott R on August 07, 2001 8:56 AM

Hope someone can help.
I have designed a spreadsheet at work with various workbooks. A form is filled in and the values are updated throughout the sheet. Then the form is cleared to start the next day, with the end results being stored throughout a month. What I am trying to do is take the end values into a separate sheet and for the values to go to the next available line.
The problem I have is I can write a macro to take the values over inserting a new line, but the further it gets down the sheet, it starts overwriting other data that is being copied over.

There must be an easier way to take end values and keep them going till the end of a month. But I am scratching my head here. Can anyone help

Posted by Scott R on August 07, 2001 8:58 AM

re-post

=RANK(Z3,$Z$3:$Z$81)<=5 is more straightforward than LARGE. I have a data set with percentages (the data set is $Z$3:$Z$81). I'm trying to apply a conditional format that will highlight the largest 5 values. I've tried the following formula in the conditional format: