Column Formula Syntax Error - Subtracting Values from Current and Previous Rows

Hello,

Need help setting up a column formula.

Everyday, my team inputs multiple values into a row. My goal is to create a column that calculates the difference of a column from today's input vs the previous day.

I am running into syntax errors trying to accomplish this (see attached image). Specifically, I want the PPM Change and PH Change to automatically calculate as you can see in red/green.

Any help would be appreciated!

Thanks


Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Brian Kim


    Is the formula you're trying to use referencing the previous row to make the calculation? If it is, that's the problem. Smartsheet (unfortunately) gets confused when you try to reference a previous row in a column formula because the formula is then applied to row 1, and the row above row 1 doesn't exist.

    The good thing is, Smartsheet will usually copy a formula down if there are a couple of rows above it with the same formula. So there's a chance you may be able to have Smartsheet still do it for you, without using a column formula.


    Best,

    Heather

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Brian Kim

    A process I like to use to solve for these kind of issues is to create 2 columns:

    1. "LINE-ID" : Auto Number Column
    2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

    This will allow you to run a check on a previous row value by using:

    Index(column:column, [ROW#]@row -1)


    In your case THE [PPM Change] column formula would be:

    =[PPM (700)]@row -INDEX([PPM (700)]:[PPM (700)],[ROW#]@row -1)

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Brian Kim


    Is the formula you're trying to use referencing the previous row to make the calculation? If it is, that's the problem. Smartsheet (unfortunately) gets confused when you try to reference a previous row in a column formula because the formula is then applied to row 1, and the row above row 1 doesn't exist.

    The good thing is, Smartsheet will usually copy a formula down if there are a couple of rows above it with the same formula. So there's a chance you may be able to have Smartsheet still do it for you, without using a column formula.


    Best,

    Heather

  • Heather - this was super helpful. You are right, Smartsheet automatically applied the "formula" based on previous cells in the row. Take Care!

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Brian Kim

    A process I like to use to solve for these kind of issues is to create 2 columns:

    1. "LINE-ID" : Auto Number Column
    2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

    This will allow you to run a check on a previous row value by using:

    Index(column:column, [ROW#]@row -1)


    In your case THE [PPM Change] column formula would be:

    =[PPM (700)]@row -INDEX([PPM (700)]:[PPM (700)],[ROW#]@row -1)

  • @Leibel S - super helpful. learned alot with this one, I will have to play around to better understand it. Thank you

  • Hi, I have two sheets, 1) captures real-time entry from another sheet - there is automation set on this sheet to move rows to a trend sheet every week. 2) A trend sheet that keeps track of each weekly entry. On this sheet, I have a column (Column6) that has the value of that week. I have another column Prev that has the formula to subtract the value in Column6 from the previous week to the newly entered value for the current week - this total is then tied to a dashboard. I am using the following:

    Auto Entry column called Entry Number

    Formula =[Column6]@row - INDEX([Column6]:[Column6], [Entry Number]@row - 1)

    If I use this formula in row 2 of the sheet, it works, but if I make it a Column formula or put this in the first row I get a #Invalid Operation error; how can I use a formula to auto-calculate new value as new rows are auto-added?


    Thank You

    Jackie

  • Hi @Jackie M

    You can write an IF statement at the front where if the current row is [Entry Number]@row = 1, then return a blank cell, otherwise calculate the formula, like so:


    =IF([Entry Number]@row = 1, "", [Column6]@row - INDEX([Column6]:[Column6], [Entry Number]@row - 1))


    Let me know if this worked for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!