Hi all,
I wonder if someone can help........
I am trying to apply conditional formatting to cells in a column. I want to highlight any cells which show a greater than 10% drop from the previous value, in the cell above.
For example: if D3 is less than 0.9 times the value of D2, then highlight the cell in red. Similarly highlight the cell when D4 is less than 0.9 times the value of D3, D5 is less than 0.9 times the value of D4, etc, etc. I have attached a section of my spreadsheet to demonstrate.
I can set the formatting for the first cell with {cell value<"0.9*$D$2"} and this highlights the cell as required.
However, when I apply the formatting to $D$3:$D$14 (in the conditional formatting rules manager) it compares each cell value to the value in D2, rather than to the cell above. So I tried deleting the dollar signs to give {cell value<"0.9*D2"}. This still compared all cells to D2. I tried using {cell value<"0.9*R[-1]C"}, but this returned an invalid formula error.
At wits end, any suggestions gratefully received.
Thanks
Bookmarks