How Do I Generate a Metrics in Dashboard Displaying a % Difference for Various Months for Two #'s

VinceA

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:

Brian_Richardson

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_Richardson

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.

VinceA

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.

Brian_Richardson

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.

VinceA

Getting an incorrect Argument for it.

Brian_Richardson

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) ,"")

VinceA

Brian .... I see what you did and get it. Much appreciated sir !! 👍️