A Problem With Trending Metrics vs Reality

thetxlibra

New Member
Joined
Jan 28, 2015
Messages
3
I have a somewhat complicated problem that I was hoping some of you may have some ideas for, both in approach and execution in Excel.

SITUATION: We have many accounts (70+) each of varying sizes and ticket volumes. Some accounts may have thousands of tickets per week, others may have 1 or less per week. All are judged by the same metric target: They must be trending downward or have a volume of zero for the most recent week.

REALITY: Let's leave aside the fact that the chances of the larger accounts' ticket volumes reaching zero are about the same as limit x -> 0, because I've given up on winning that argument. The smaller account managers find it unacceptable that they are flagged as failing a metric when they only have 0 or 1 ticket per week, and are trending at +0.1 tickets per week (which honestly could change within a few weeks regardless, because again, it's often 0). But the fact is, according to the metric target, if the current week's tickets are not 0, and they are trending upward in volume, they shall not pass!.

ATTEMPTS AT REMEDIATION: We have done the following to attempt to alleviate these kinds of issues:
  • Calculate the FDIST via LINEST for both 8 and 16 weeks separately, determining a trend only if the FDIST is lower than 0.05 and if both are, we use the lowest one to determine slope via LINEST.
  • We then check the slope against a "shallowness" threshold. If the slope is less than a 0.005 difference (half a percent), it is determined to be flat (because, seriously, a trend of 0.007 tickets per week is useless to us, no matter how low the FDIST is.
  • We have even snipped out blank weeks and weeks with inapplicable data types (such as alpha characters). That's just best practices, but figured I'd go ahead and answer that question before it got asked.

PROBLEM: Despite these attempts, we still have several accounts getting ding'd each week for failing a metric when in reality, they should be quite happy about the numbers. The logical choice would be for the target to give more of a forgiveness threshold, such as "If it's under X tickets, don't call it a failure even if its trending upward." But we can't for a few reasons:
  • The rule must be consistent and attainable by all accounts, and if we set the number at, say, 5 tickets, there's still no way that we can get the larger accounts down to that point.
  • I don't have authorization to change the target anyway, and those that do, won't.
  • Large accounts have a natural limit they are going to reach before they cannot reasonably be expected to get any better. For instance, an account with 8000-10000 tickets per week is unlikely to ever get below 2000 tickets per week, short of a holiday week where the entire company is shut down, and that's an outlier. So if that large account has a natural limit, it needs to be determined somehow with only 16 weeks worth of data at a time.

DESIRED SOLUTION: I am looking for a non-linear way to calculate a trend that will work for any size account, and determine its natural limit, and as it approaches that natural limit, beyond a certain threshold, determines the threshold to be flat even though a linear trend may determine it to be moving up or down. Sort of like a built-in law of diminishing returns or an inverse logarithmic function.

For those who haven't already moved on with TL;DR, I would greatly appreciate your help in this.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

I am no expert but it sounds as if you want to be comparing shapes rather than absolute values.

Take a look at this to get some ideas: Normalization (statistics) - Wikipedia, the free encyclopedia

For instance, the "Standard Score" formula modifies all the sample values by subtracting the mean then dividing by the standard deviation. This will change all your accounts' figures to have a mean of zero and a standard deviation of one.

After that you could find slopes etc of the resulting samples and then compare like with like. Exactly which normalization formula you need will depend on your data.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,836
Messages
5,766,714
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top