Greater Than, Less Than, or Equal To previous month value

Luke W.

Hello,

I'm looking for help with the following formula to show trending information.

12 Months of Data (Jan - Dec), When each month gets updated, I would like to use a formula to show symbols for trending up, down, or unchanged compared to the previous month.

Does anyone know of a formula that can point me in the right direction?

Thank you.

Paul Newcome

Can you provide a screenshot of how your data is currently laid out?

Luke W.

Paul Newcome

So we want to pull from current_month...

INDEX(Jan@row:Dec@row, 1, MONTH(TODAY()))

And compare to previous_month...

INDEX(Jan@row:Dec@row, 1, MONTH(TODAY()) - 1)

and drop that comparison into a nested IF statement to show no change if equal, up if up, and down if down.

=IF(current_month = previous_month, "Unchanged", IF(current_month < previous_month, "Down", IF(current_month > previous_month, "Up")))

=IF(INDEX(Jan@row:Dec@row, 1, MONTH(TODAY())) = INDEX(Jan@row:Dec@row, 1, MONTH(TODAY()) - 1), "Unchanged", IF(INDEX(Jan@row:Dec@row, 1, MONTH(TODAY())) < INDEX(Jan@row:Dec@row, 1, MONTH(TODAY()) - 1), "Down", IF(INDEX(Jan@row:Dec@row, 1, MONTH(TODAY())) > INDEX(Jan@row:Dec@row, 1, MONTH(TODAY()) - 1), "Up")))

Luke W.

Thanks! This is great!

I pasted the formula and it's giving me the opposite results of what I want. I'll get a screenshot shortly to show you the results.

I understand the syntax, so I'm not sure what is going on.

Luke W.

You'll see that the first row is showing "down" but should be "Unchanged".

Second row should be "up", but is showing "down".

Not sure what is going on....

Paul Newcome

How exactly are the numbers being calculated?

Luke W.

https://community.smartsheet.com/discussion/comment/282544#Comment_282544

Monthly Data Values are populated via Form Entry.

YTD is just an average of JAN:DEC

Paul Newcome

Ok. When a user enters the number into the form, are they just entering a number and the field is automatically converted to a percentage, or are they actually entering the % symbol? If they are manually entering the % symbol, then it is going to be stored as a text value instead of a numerical value.

Luke W.

They should be just entering a number, but I guess some of them could be adding the % symbol.

I have the values formatted in the source sheet.

Paul Newcome

Try running a test and updating some of those rows with unexpected results manually so that you are sure they are numerical values.