Forum Discussion

Clare Harwood's avatar
Clare Harwood
Copper Contributor
Jan 05, 2018

setting icon set limits using formulae?

Hi there

I want to set up a status report to update each quarter and want to use condition formatting to show how on trackthe organisation is depending on where we are in the year. What i want to be able to do is use the red/amber/green icon set, but set the values it uses to determine which colour to display, depending on which quarter we are in.

 

eg.  If our target is 10,000 ... and we are currently at 3,000 - then at Q1 (25% of target) that 3,000 would show as Green as we would have acheived 30%, at Q2 (50%) it would be amber, and - if we'd only managed 3,000 and we were in Q3 (75%) then it would show a definite red!  

 

I don't know how to use formula to set these limit figures in the icon set.  Can this be done?  and if so, how would I do it?

 

Many thanks

Clare

 

excel

2 Replies

Sort By
  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Jan 08, 2018

    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

     

     

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 05, 2018

    Hi Clare,

     

    Without concrete sample hard to say. As variant and very approximate -

    let say you calculate <% achieved> and <time zone> for which it is calculated (.25 for Q1; 0.5 for Q2; 0.75 for Q3 and 1 for Q4). When <annual approximation> = <% achieved>/<time zone>.

     

    After that you create three rules in conditional formatting (one for each color) using a formula, e.g. for red

    =<annual approximation> <= 0.25

    select format and apply to cell(s) to highlight.

     

    Something like this.

     

     

Resources