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

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)

Tags:

Answers

  • James Keuning
    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

  • 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
    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.
    Genevieve P. Employee
    edited 01/11/24

    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)

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!