I have a sheet1 with columns labeled "Metric Name," "Plan-Actual," and monthly columns where we record the plan and actual numbers consecutively for the same metric name.
Metric Name | Plan - Actual | Last FY - Jun | Last FY - Jul | Last FY - Jaug |
|---|
Test-01 | Plan | 90 | 89 | 21% |
Test-01 | Actual | 90 | 79 | 78 |
Test-02 | Plan | 90 | 100 | 120 |
Test-02 | Actual | 80 | 70 | 80 |
Now in sheet2, I need to create a difference column for each metric where the calculation will be:
100 -(Actual(Test-01)/Plan(Test-01))*100
For example, for the Test-01 metric, if the values for Last FY - Jun are Actual = 90 and Plan = 90, the calculation would be:
100 - ((90/90)*100) = 0
Metric Name | Last FY - Jun |
|---|
Test-01 | 0 |
Test-02 | 11 |
Any workaround to get the result would be helpful!
