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

Brian Kim

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

Heather Duff

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

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

Heather Duff

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

Brian Kim

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

Leibel S

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

Brian Kim

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

Jackie M

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

Genevieve P.

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