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 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!

Find more posts tagged with

Sheets

Formulas

Dashboards

Sheet Summary

Cell linking

Accepted answers

Samuel Mueller

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"), "")

All comments

Samuel Mueller

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

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!

Samuel Mueller

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"), "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.