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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!