Deviation% for planned vs actuals, custom threshold RAG status

siyas

New Member
Joined
Jun 2, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am working on an KPI which shows deviation% from planned cumulative spend vs actual cumulative spend by month for a project.
Custom threshold is as follows, if deviation% is
1. between >=-10% and <=10%, then Green
2. between <=-11% and >=11%, then Yellow
3. between <=-20% and >=20%, then Red

I inserted pivot on raw data for cumulative planned vs actual by month and did deviation% by cell referencing and custom formatting of threshold outside pivot.
However, when I select different project, I have 3 issues:
1. sometimes, the deviation% and custom R/Y/G indicator doesn't update as per above given threshold.
2. sometimes, deviation% updates but the R/Y/G indiactor doesn't update.
3. another requirement is to change the color of the deviation% in R/Y/G color. I cannot do this like making value green for both +-10%.


Someone, please guide me to a better workable formula and soultion to my 3 stated problem.
Much thanks in advance.

Regards.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,141,703
Messages
5,707,963
Members
421,538
Latest member
Krisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top