Need Help Calculating % Change (increase or decrease) Between Two Numbers

kerobi

Hi everyone -- I am new to Smartsheet and writing formulas and I've searched high and low in the community here to try to figure out how to calculate the % change, increase or decrease. I need to pull WoW numbers for a report to monitor the ups or downs in that %, but I'm having a difficult time figuring this out.

I first tried using an ABS formula to per a community member, but that doesn't seem to be exactly right because once I applied the formula down that column the % change is not an accurate percent (e.g., % change from 20 to 24 is not 17%, which is what the ABS calculation is getting me, it should be 20%).

TRIED THIS FORMULA BELOW - NO GOOD -- HOPING SOMEONE HAS OTHER SUGGESTIONS. Thank you!!

subtract X from Y to get an absolute value "V"

get the MAX of "X" and "Y" [to get the greater of the two]

"V"/(MAX of "X" & "Y") [format this column as percentage ---- don't forget to increase your decimal place accuracy]

=ABS([Column2]21 - [Column3]21) / MAX([Column2]21:[Column3]21)

James Keuning

Can you provide sample data, I am not sure why you are using MAX, and why this does not work:

=ABS([Number 2]@row - [Number 1]@row) / [Number 1]@row

kerobi

Thanks, James. But yesterday, a colleague happened to have just done this for another team, so sharing below in case someone else needs the answer.

Value A-Value B/Value B

=([Column2]@row - [Column2]90) / [Column2]90 -- then make your column %, works like a charm!

Rwoo

I know this is an old thread, but I'm having a similar issue. I already had the formula above, but I'm looking to return a blank cell if the divisor is zero. In another instance, if the difference is actually 0%, I'd like to see 0%.

Starting with =([Column2]@row - [Column2]90) / [Column2]90

Any ideas are appreciated, thanks!

Genevieve P.

Hey @Rwoo

You can add a statement at the beginning that ensures the divisor is greater than 0:

=IF([Column2]90 = 0, "", ([Column2]@row - [Column2]90) / [Column2]90)