Referencing the Row Above in a Formula
Hi all!
I am trying to create a formula that compares 2 values in the same column. One on the current row, to one on the row right above it. Every row represents a separate week, and I want to compare metrics from the current week, to last week, and based on the result (>,<,=) a symbol will appear. I found a post where someone was trying to do something similar, however I was not able to retrofit it into my IF statements. If I could get a little guidance and help with this, it would be greatly appreciated! Below please find screenshots as well as the equation I am trying to use:
Equation:
=IF(([AVG TIME PREPO]@row < [AVG TIME PREPO]@row  1), "Up", IF(([AVG TIME PREPO]@row > [AVG TIME PREPO]@row  1), "Down", IF(([AVG TIME PREPO]@row = [AVG TIME PREPO]@row  1), "Sideways", IF(ISBLANK([AVG TIME PREPO]@row  1)), "Sideways")))
Here, I tried to reference the row above by using @row1, however this just reduced the value at the row by 1 rather than referencing the row above. The "Up","Sideways", and "Down" strings represent symbols.
As for the values being compared, there is nothing special going on there, they are just numbers.
Any and all help would be greatly appreciated, ty!
Best Answer

You might need another helper column for your situation, but one way to do this is
=if([AVG TIME PREPO] = INDEX([AVG TIME PREPO]:[AVG TIME PREPO], Row@row  1),"Sideways")
and you will probably want this wrapped in an iferror because if it's the first row you will get an error
=IFERROR(if([AVG TIME PREPO] = INDEX([AVG TIME PREPO]:[AVG TIME PREPO], Row@row  1),"Sideways"), "")
Answers

You need two columns, an autonumber column, and another column called row. In the column called row the formula will be
=match([autonumbercolumn]@row, [autonumbercolumn]:[autonumbercolumn],0)
This gives you the row, which you can then reference in your formula to return data from the row you want

Hi Samuel!
This is actually the response I was looking at earlier, but did not know how to fit into my formula. So as it stands (I had forgotten to mention this in the original post), I do have the two columns you described above, however, I don't know how to reference it within the formula (I suppose this is a question of syntax). So to take one line from the formula, how would I incorporate your logic into the following:
IF(([AVG TIME PREPO]@row = [AVG TIME PREPO]@row  1), "Sideways")
Thank you for your quick response, and I am sorry for my confusion, I really appreciate the help!

You might need another helper column for your situation, but one way to do this is
=if([AVG TIME PREPO] = INDEX([AVG TIME PREPO]:[AVG TIME PREPO], Row@row  1),"Sideways")
and you will probably want this wrapped in an iferror because if it's the first row you will get an error
=IFERROR(if([AVG TIME PREPO] = INDEX([AVG TIME PREPO]:[AVG TIME PREPO], Row@row  1),"Sideways"), "")
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!