Variance Formula in Sheet Summary

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!

Find more posts tagged with

Accepted answers

All comments

Neil Watson

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.