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

  • Hollie Green
    Hollie Green 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

  • Hollie Green
    Hollie Green 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)), "")

  • mcullen
    mcullen ✭✭✭✭✭

    @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!