How to use conditional formatting for bi-directional KPI's - red yellow green
- Thread starter mg2017
- Start date
David Evans
Active Member
Post an example file of what you're trying to achieve - it will save you and us time in getting to an answer.
Hi thank you for having a look at my question. I have uploaded the file. The regional average is the centre point where I was wanting to create yellow green. red bands on either side. Over utilization may be just as bad as underutilization of various resources or prevalence rate ( under may mean there are not enough doc's available) . Again my thanks.
@mg2017
Interesting question. Unfortunately, you can't link icon rule to both directions like this in one cell. A simpler alternative is to display icon in a separate column. That way, you can calculating absolute % change from regional value and then display relevant icon, like this:
To do this,
Let's say your region value is in $E5 and location value is in K5.
Calculate Absolute % difference using =ABS(K5-$E5)/$E5
Now set up a conditional formatting rule to show
Repeat this process (just copy paste formatting) for rest of the locations.
See attached workbook.
Interesting question. Unfortunately, you can't link icon rule to both directions like this in one cell. A simpler alternative is to display icon in a separate column. That way, you can calculating absolute % change from regional value and then display relevant icon, like this:
To do this,
Let's say your region value is in $E5 and location value is in K5.
Calculate Absolute % difference using =ABS(K5-$E5)/$E5
Now set up a conditional formatting rule to show
- red icon if when value is >0.2
- amber when value is >0.1
- and green when value is <=0.1
Repeat this process (just copy paste formatting) for rest of the locations.
See attached workbook.
David Evans
Active Member
Here's a slightly different technique, but again using CF and formulas.
I took the liberty of automating the Target %, so if you decide to alter the ranges from 10% to some other number, the CF will follow suit. I also removed some extraneous cells that were essentially duplicates of the C:K ranges and were confusing the issue.
I left row L9:AR9 in your formatting technique - if you want to include it in the new version, go into each CF and amend the effective range from L10:AR15 to L9:AR15.
I took the liberty of automating the Target %, so if you decide to alter the ranges from 10% to some other number, the CF will follow suit. I also removed some extraneous cells that were essentially duplicates of the C:K ranges and were confusing the issue.
I left row L9:AR9 in your formatting technique - if you want to include it in the new version, go into each CF and amend the effective range from L10:AR15 to L9:AR15.
Hi thank you for taking the time to look at this. I applied the previous method to the spread sheet and now I am re-thinking having everything bi-directional (for example the outcomes - I think should just be one direction). So again my sincere thanks. I appreciate all of the help you and R2C2 have provided.
Have a good evening!
Have a good evening!