Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Calculating the Difference Between Current Row and Previous Row

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

  • Community Champion
    edited 01/29/25 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

  • Community Champion
    edited 01/29/25 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)), "")

  • ✭✭✭✭✭

    @Hollie Green, thank you! I will give it a try.

    Michelle Cullen

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions