Column Formula Possibility

Kimberly Loveless

I am looking to create a column formula to subtract the bottom score (Q1-Score) from the one in the line above if the values in the Survey Identifier field are the same (the formula would be going in the Q1- Dif column).

Right now I have the formula below which works as a drag and drop field but I would like it to be a column formula field if possible. How can I change the formula to make it be a column formula?

=IF([Survey Identifier]@row = [Survey Identifier]3, [Q1- Score ]3 - [Q1- Score ]@row)

(This is the formula in row 4)

Nathaniel Adamian

Hi Kimberly,

Unfortunately smartsheet doesn't currently support cell references in column formulae so you won't be able to make this a column formula while using references like [Survey Identifier]3.

To get around this you'll need to use a slightly more complicated set of functions. There might be an easier way to do this but I'd suggest using two helper columns so that you can dynamically reference row numbers. First create an auto number column Auto Num. Then create a second column called Row Num, this is your row number column. Set the following formula as a column formula for your Row Num column:

=MATCH([Auto Num]@row, [Auto Num]:[Auto Num], 0)

Once you have a Row Number column you can rewrite your formula as:

=IFERROR(IF([Survey Identifier]@row = INDEX([Survey Identifier]:[Survey Identifier], [Row Num]@row - 1),

INDEX([Q1 - Score]:[Q1 - Score], [Row Num]@row - 1) - [Q1 - Score]@row), 0)

This should always reference the score in the above row if the survey identifiers match. I included the IFERROR to avoid an error in the first row, which has no row above it, though there might be a more elegant way to get around that as well.

Best,

Nathaniel