Referencing the Row Above in a Formula

Muhammad
Muhammad ✭✭✭
in Formulas and Functions

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 PRE-PO]@row < [AVG TIME PRE-PO]@row - 1), "Up", IF(([AVG TIME PRE-PO]@row > [AVG TIME PRE-PO]@row - 1), "Down", IF(([AVG TIME PRE-PO]@row = [AVG TIME PRE-PO]@row - 1), "Sideways", IF(ISBLANK([AVG TIME PRE-PO]@row - 1)), "Sideways")))

Here, I tried to reference the row above by using @row-1, 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!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 02/29/24 Answer ✓

    You might need another helper column for your situation, but one way to do this is

    =if([AVG TIME PRE-PO] = INDEX([AVG TIME PRE-PO]:[AVG TIME PRE-PO], 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 PRE-PO] = INDEX([AVG TIME PRE-PO]:[AVG TIME PRE-PO], Row@row - 1),"Sideways"), "")

    · Share on FacebookShare on Twitter

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers

    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

    · Share on FacebookShare on Twitter
  • Muhammad
    Muhammad ✭✭✭
    https://community.smartsheet.com/discussion/comment/422592#Comment_422592

    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 PRE-PO]@row = [AVG TIME PRE-PO]@row - 1), "Sideways")

    Thank you for your quick response, and I am sorry for my confusion, I really appreciate the help!

    · Share on FacebookShare on Twitter
  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 02/29/24 Answer ✓

    You might need another helper column for your situation, but one way to do this is

    =if([AVG TIME PRE-PO] = INDEX([AVG TIME PRE-PO]:[AVG TIME PRE-PO], 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 PRE-PO] = INDEX([AVG TIME PRE-PO]:[AVG TIME PRE-PO], Row@row - 1),"Sideways"), "")

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!