Conditional Highlighting
How do I set up conditional highlighting for cells that are a specified % greater than comparative cell?
iMac, OS X 10.11
Question marked as
Top-ranking reply
Assuming the cells to be highlighted are in a column, the easiest way is to add a column to the table to hold the actual values that have to be met by each cell to be highlighted, then base your CH rule on the values in the new column.
Here's an example.
Column A contains the base values. For simplicity, I've set them all to 1000.
Column C contains the values whose cells are to be highlighted if they are (at least 30% greater than the value in the corresponding row of column A.
Column C, which will be hidden, contains a formula that adds the specified percentage to the values in the same row of column A, and that the values in Column C will be compared with by the conditional formatting rule applied to that column.
The specified percentage in this case is 30%, shown in cell C1
The formula shown below the table is entered in D2 and filled down to the end of that column.
To set the rule, Select the range of cells to be highlighted (C2 to C10)
Click on Add a rule
Choose: Number is Greater than*
click the icon at the right end of the next box, then click the topmost cell containing the value to compare (D2).
Choose the type of highlighting you want from the box showing 'Green Fill'.
Click Done.
*If you want to highlight a value that meets the target, choose 'Greater than or Equal to' in place of Greater Than.
Hide column D.