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:
- "LINE-ID" : Auto Number Column
- "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
-
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:
- "LINE-ID" : Auto Number Column
- "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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!