Calculation for Variance in Percentage between Target column and YTD Column

Sophia Strickland

Hello Community;

I'm building a dashboard to reflect how different projects/programs are tracking towards targets. I want to showcase the following: 1) Projects that have gone above their targets, 2) Projects that didn't meet the target, 3) Projects On track to meeting the targets.

I've already tried this formula =SUM([Actual column - [Target Column) / [Target Column] . Examples below:

Project 1 - Actual 165 - Target 70 = 136% over performed

Project 2: Actual 99 - Target 120 = -18% under performed

Project 3: Actual 50 - Target 50 = 0% Target met (instead of 100%)

Is there a better way to reflect the above information? Any help is greatly appreciated.

Thank you;

Genevieve P.

Hi @Sophia Strickland

It sounds like you have a helper sheet that's creating your % calculation, is that correct?

If so, then if you want to display each grouping with the individual project names, I would suggest creating 3 Reports. The first Report could show all Projects that have positive percents (meaning they over performed), then second Report's criteria would be negative percents, and the final Report's criteria would show all Projects that = 0.

Then if you wanted to create Charts out of this, as long as your Actual and Target columns are presenting numerical data, you could do something like the following:

If you wanted to see overall totals, without parsing it out per-project, you would need to set up a little helper chart in your sheet to create those calculations with a COUNTIF formula, like this (I've spelled out each formula in the far right column):

Then you can use this numerical data to display it in a Pie Chart, or something similar:

(Note: the first two charts were made directly from your Actual and Target column data, without needing the helper % column. The third, Pie Chart is made from the COUNTIF helper table above.)

Let me know if this makes sense and will work for you, or if you have more questions. If this doesn't work, it would be helpful to see a screen capture of how your sheet it set up with the projects and data listed, but please block out any sensitive data.

Cheers!

Genevieve