How Do I Generate a Metrics in Dashboard Displaying a % Difference for Various Months for Two #'s
Howdy Folks,
I'm not all that prolific but am able to get around formulas and dashboard creation to a small degree. I however am getting stuck with needing to reflect the difference between two #'s as an Average of Monthly %'s to display as a Metric. I do have a Chart with just the #'s I'm displaying but want a way to display the % difference too as either a Chart or Metric call-out.
I thought perhaps that maybe there could be a formula directly on the Metrics sheet that would pull the data from other areas of the same Metric sheet and show it as a %, and then I could display it onto the dashboard as a Chart or Metric # as a %.
Reason for this is that I am getting different % Averages by pulling the data from my Archive sheet used to build this Metric sheet. In other words I am getting 85% for the month of SEPT while the true % difference is @ 56%.
Thanks in advance.
----------------------------------------------------
DASHBOARD:
METRICS SHEET:
ARCHIVE SHEET:
Best Answer
-
That's because you have an IF statement in there without it's various arguments. Get rid of the IF and you should see the result you're looking for. You can wrap it in an IFERROR statement if you want to remove errors like DIVIDE BY ZERO.
=IFERROR(([Column 9]32-[Column 9]28)/[Column 9]28,"")
Or if you want to use IF to check for things like 0 values like you have on your archive sheet, you need to finish the IF statement. Like
=IFERROR(IF(([Column 9]32-[Column 9]28)/[Column 9]28 =0, "", ([Column 9]32-[Column 9]28)/[Column 9]28) ,"")
Answers
-
It's a little hard to follow this but it looks to me like you're doing two different things and expecting them to be the same.
On the top sheet you're pulling in SUM and AVG data and then taking a difference to get 56%. But then further down you calculate the difference first on your Archive sheet, then take an average of multiple rows onto your top sheet.
Instead maybe take the % difference on the top sheet as a difference of the SUM/AVG numbers you already pulled into the sheet, so you have only 1 calculation method rather than 2.
-
Hey Brian,
yes, I have a way of making things complicated, bad tendency when I need to keep to simplicity.
So basically I'd like to know how and if I can take the SUMMARY rows as per above (ex./ Month of September 2864 - 1837 divided by 1837) and have a formula on this Metrics page to render the results.
Please let me know if I still need to simplify what I'm aiming for.
Thanks much.
-
Unless I'm missing something you should just be able to do the math on the page, using the average or sum numbers that you already pulled in. So ([Column 9]6-[Column 9]2)/[Column 9]2 . Then drag that formula left and right across your sheet and it should update itself with the correct column names for each column.
-
Getting an incorrect Argument for it.
-
That's because you have an IF statement in there without it's various arguments. Get rid of the IF and you should see the result you're looking for. You can wrap it in an IFERROR statement if you want to remove errors like DIVIDE BY ZERO.
=IFERROR(([Column 9]32-[Column 9]28)/[Column 9]28,"")
Or if you want to use IF to check for things like 0 values like you have on your archive sheet, you need to finish the IF statement. Like
=IFERROR(IF(([Column 9]32-[Column 9]28)/[Column 9]28 =0, "", ([Column 9]32-[Column 9]28)/[Column 9]28) ,"")
-
Brian .... I see what you did and get it. Much appreciated sir !! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!