How to create a formula column that calculates the difference between rows?

sweta22

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!

sweta22

It is working! Thanks much

Nick Korna

Hi @sweta22,

You can use SUMIFS for this.

Doing the formulas in the same sheet:

=IFERROR(100 - ((SUMIFS([Last FY - Jun]:[Last FY - Jun], [Metric Name]:[Metric Name], [Metric Name 2]@row, [Plan - Actual]:[Plan - Actual], "Actual") / (SUMIFS([Last FY - Jun]:[Last FY - Jun], [Metric Name]:[Metric Name], [Metric Name 2]@row, [Plan - Actual]:[Plan - Actual], "Plan")) * 100)), "")

Obviously for a separate metric sheet you'd use some cross sheet references instead of column ones, but hopefully this gives you the gist of how to do it. If you've any problems/questions still, let us know!

sweta22

It is working! Thanks much