How to create a formula column that calculates the difference between rows?
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!
Best Answer
-
It is working! Thanks much
Answers
-
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!
-
It is working! Thanks much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!