I’m in the process of setting up a project management board that will include calculations for Earned Value Management. I have two issues that I’m trying to solve for that both relate to formulas presenting undesired values or errors when calculating work that has not yet been been started.
Scenario #1: Calculating Schedule Variance
Schedule Variance is calculated with the following formula - {Earned Value}-{Planned Value (BAC)}. However, if the task has not yet been started, the {Earned Value} columns will be blank and I would rather not perform the calculation at all and, instead, show a dash or blank cell. If there is a value, however, I would like to calculate as displayed above - {Earned Value}-{Planned Value (BAC)}.
Scenario #2: Calculating Cost Performance Index (CPI)
This is similar to the one above but involves division. I am calculating CPI with the following formula - {Earned Value}/{Actual Cost}. In the same manner, when the {Earned Value} or {Actual Cost} columns are blank as a result of work not having started, I would prefer not to run the calculation and present a dash or a blank cell. As apposed to the one above (avoiding a negative number), I’m trying to also get rid of the red alert (!), while not performing the calculation (which should take care of itself). Like above, I would still like to run the calculation {Earned Value}/{Actual Cost} if both cells have values.
I’ve attached a screen grab for reference.
Thank you ahead of time for any help.
