Hey Everyone,
Perhaps I'm suffering from some tunnel vision and forgive me for the wordy explanation... but I could use some assistance. I'm needing to calculate the Percentage Point difference between the top and the bottom performers across a series of different stats. This is normally very simple if the stat is already represented in a percentage format. The challenge that I'm having is using the same formula (for consistency) on data that isn't inherently in a % format (like Gross Sales Revenue example below).
I am trying to measure the performance of a group of people to get them within a 5% Point Difference of each other, when the difference of the Top Sales performer and the bottom Sales performer is reflecting $645k difference in a currency format instead of a Percentage format. (example below)
I tried switching this to a Percent Variance calculation and the outliers in performance are causing a large % of variance and isn't the same message as Percentage Point Difference.
So I guess I need insight/suggestions on out-of-the-box ways that you've seen/done to measure a gap in performance and account for outliers that would work on large revenue numbers as well as numbers already in a percentage format. And it would need to be something that would appropriately scale when communicated in a percentage Point Difference.
The two formulas that are most favored so far are below the sample data, but it still feels like there is an option I'm overlooking.
<tbody>
</tbody>
Perhaps I'm suffering from some tunnel vision and forgive me for the wordy explanation... but I could use some assistance. I'm needing to calculate the Percentage Point difference between the top and the bottom performers across a series of different stats. This is normally very simple if the stat is already represented in a percentage format. The challenge that I'm having is using the same formula (for consistency) on data that isn't inherently in a % format (like Gross Sales Revenue example below).
I am trying to measure the performance of a group of people to get them within a 5% Point Difference of each other, when the difference of the Top Sales performer and the bottom Sales performer is reflecting $645k difference in a currency format instead of a Percentage format. (example below)
I tried switching this to a Percent Variance calculation and the outliers in performance are causing a large % of variance and isn't the same message as Percentage Point Difference.
So I guess I need insight/suggestions on out-of-the-box ways that you've seen/done to measure a gap in performance and account for outliers that would work on large revenue numbers as well as numbers already in a percentage format. And it would need to be something that would appropriately scale when communicated in a percentage Point Difference.
The two formulas that are most favored so far are below the sample data, but it still feels like there is an option I'm overlooking.
| Employee | Revenue | % to Rev Goal | Revenue Goal |
| Salesperson 1 | 275590.64 | =SUM(B2/D2) | 500000 |
| Salesperson 2 | 223687.99 | =SUM(B3/D3) | 500000 |
| Salesperson 3 | 355685.14 | =SUM(B4/D4) | 500000 |
| Salesperson 4 | 38658.22 | =SUM(B5/D5) | 500000 |
| Salesperson 5 | 272800.49 | =SUM(B6/D6) | 500000 |
| Salesperson 6 | 500215.99 | =SUM(B7/D7) | 500000 |
| Salesperson 7 | 2297.56 | =SUM(B8/D8) | 500000 |
| Salesperson 8 | 191519.39 | =SUM(B9/D9) | 500000 |
| Salesperson 9 | 647587.01 | =SUM(B10/D10) | 500000 |
| Salesperson 10 | 210512.61 | =SUM(B11/D11) | 500000 |
| Salesperson 11 | 297947.96 | =SUM(B12/D12) | 500000 |
| CURRENTLY BUILT | |||
| Difference | =SUM(MAX(B2:B12)-MIN(B2:B12)) | 645289.45 | |
| OTHER OPTIONS | |||
| Percent Variance (Spread) | =SUM(MAX(B2:B12)-MIN(B2:B12))/(MIN(B2:B12)) | 280.858584759484 |
<tbody>
</tbody>
