Variance Formula in Sheet Summary

Megan Harry

Hello,

I love to utilize sheet summaries to link metrics to the dashboards I create. I want to have quarter over quarter and year over year variances in my sheet summaries. I thought I had figured out how to do it, but when I was viewing one of my dashboards I realized the percentages were off.

sheet summary.png

I have attached a screen shot of what I have been trying to do. I am trying to do (a sheet summary field-minus another one)/the subtracted field. I have been using the formula below. I use IFERROR because sometimes I have them divide by zero. So what I am trying to do in the image is (7-88)/88. This should give me -92%, but instead it is giving me 600%.

=IFERROR([2021 Q1 NOFs]# - [2020 Q4 NOFs]# / ABS([2020 Q4 NOFs]#), "0")

Is there an issue with the formula I am using?

I would appreciate the help!

Neil Watson

HI

Did you try to add parenthesis around the first term in your formula?

=IFERROR(([2021 Q1 NOFs]# - [2020 Q4 NOFs]#) / ABS([2020 Q4 NOFs]#, "0")

L_123

OOO

Order of operations

/ comes before -

iferror(([2021 Q1 NOFs]# - [2020 Q4 NOFs]#)/ ABS([2020 Q4 NOFs]#), 0)

7 - 88/88 = 7 - 1 = 600%

Megan Harry

@Neil Watson @L@123 123

That was my exact issue. Thank you for the help! It worked!