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

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

A process I like to use to solve for these kind of issues is to create 2 columns:
 "LINEID" : Auto Number Column
 "ROW#" : Column Formula: =MATCH([LINEID]@row, [LINEID]:[LINEID], 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

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!

A process I like to use to solve for these kind of issues is to create 2 columns:
 "LINEID" : Auto Number Column
 "ROW#" : Column Formula: =MATCH([LINEID]@row, [LINEID]:[LINEID], 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 realtime 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 autocalculate new value as new rows are autoadded?
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
Categories
Check out the Formula Handbook template!