I’m making a table as below, in the table there are the following indicators: Target, Actual Result, Add-on Result, One time Add-on Result, Total Result, Progress and Completion Rate.
- Total Result will be calculated by
SUM(Actual Result + Add-on Result + One time Add-on Result) - Completion Rate will be calculated by
ROUND((((Total Result - Add-on Result - One time Add-on Result)/Progress) + Add-on Result + One time Add-on Result)/ Target,4).
I want to set limits of completion rate to 100% so I need to change Add-on Result (not One time Add-on Result). How can I calculate Add-on Result to match with my condition?
| Target (A) | Actual Result (B) | Add-on Result (C) | One Time Add-on Result (D) | Total Result (E) = (B) + (C) + (D) | Progress (F) | Completion Rate(G) = Round((((E-C-D)/F)+C+D)/A,4) |
|---|---|---|---|---|---|---|
| 5,406 | 3,468.63 | 1,500 | 207.2 | 5,175.83 | 82.1% | 109.7% |
So I want to reduce Add-on Result to make Completion Rate as 100%. It will not be 1,500 but something. But I don’t know how to calculate.
>Solution :
Would you not just do:
=A1-D1-(B1/F1)
- A1 is the Target.
- D1 is subtracted for the One Time Add-on Result.
- (B1/F1) adjusts the Actual Result based on Progress.