setting icon set limits using formulae?

Hi Clare

Please see attached sample file I've mocked up. Here's the break down in steps:

1. I've formatted the table below and added a simple formula that divides the total Target amount by Year to Date (YTD) which gives me a percentage amount in % Annual Target.

Formula reads as =(E8/$E$5) for the cell in column E, =(F8/$E$5) for column F, etc.

2. Clicking onto the cell E9 (sorry the numbers aren't on the left in the screenshot), I went to the Conditional Formatting menu and added the following conditions for each QTR headers under the % Annual Target cells.

Please note that I have also added a range for amber to specify the warning zone versus really far behing our target flag. It's a value add, you don't need to use it if you don't want to.

For Cell E9 (Q1)

** Note: Cell Value between 0.2 and 0.24 is to highlight that range in amber.

For Cell F9 (Q2)

** Note: Cell Value between 0.4 and 0.49 is to highlight that range in amber.

For Cell G9 (Q3)

** Note: Cell Value between 0.7 and 0.74 is to highlight that range in amber.

For Cell H9 (Q4)

** Note: Cell Value between 0.9 and 0.99 is to highlight that range in amber.

3. You can adjust the Target value and any of the YTD values and the formula will reflect the above no matter what. If you exceed your targets beyond 100%, the conditional formatting will show the cells as green.

I hope that this is what you were after!

Cheers

Damien