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)
Answers
-
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!
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!