Calculating the Difference Between Current Row and Previous Row
![mcullen](https://us.v-cdn.net/6031209/uploads/userpics/E1MTOINH0CYD/nILRWU66XD1HG.png)
Hello fellow community members!
I'm trying to write a formula that will calculate the "Difference" and "Balance Difference" between the current and previous rows "Total Invoice Count" and "Total Balance". The following formulas have worked well on the cell level:
=[Total Invoice Count]@row - [Total Invoice Count]1
=SUM([Total Balance]@row - [Total Balance]1)
I'm able to drop and drag the formulas above to the proceeding rows, but I'd like it to be a column formula instead of a drop and drag. When I select "Convert to Column Formula", I get a notice stating, "The column formula syntax isn't quite right". I've tried numerous combinations without success.
Ideally, the formulas would calculate the "Difference" between the current and previous rows "Total Invoice Count" and the "Balance Difference" between the current and previous rows "Total Balance"; if the current or previous row is blank or zero, leave the cell blank. Any help would be greatly appreciated!
Michelle Cullen
Best Answer
-
The easiest way is to set an auto row column to reference in your formula. Below is an example of the formula if you title your Auto Row as Row # and it counts from top to bottom starting at 1.
=IFERROR([Total Invoice Count]@row - (INDEX(COLLECT([Total Invoice Count]:[Total Invoice Count], [Row #]:[Row #], [Row #]@row - 1), 1)), "")
Answers
-
The easiest way is to set an auto row column to reference in your formula. Below is an example of the formula if you title your Auto Row as Row # and it counts from top to bottom starting at 1.
=IFERROR([Total Invoice Count]@row - (INDEX(COLLECT([Total Invoice Count]:[Total Invoice Count], [Row #]:[Row #], [Row #]@row - 1), 1)), "")
-
@Hollie Green, thank you! I will give it a try.
Michelle Cullen
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!