I need a stats major (or something like that)

[JonInMiddleGA]

Okay, I'm too tired, too grumpy & generally too brain-fried to think this through on my own, so I'm hoping somebody can just provide a Dummy's Guide to how to approach this situation.

Here's what I've got:
-- An Excel spreadsheet
-- A column of 113 values, ranging from $836.12 down to $0.29.
-- I know how to get Excel to compute standard deviation (in this example, that's $149.03)

Here's what I think I know:
-- that you could logically create groups of values by the SD's; i.e. items within one SD, items between 1SD & 2SD, between 2SD & 3SD, etc.

Here's where my brain is going fuzzy (if it hasn't done so already):
-- the mean of the set is $12.68, basically there's no item below the mean more than 1 SD, but a couple of outliers on the high end are 5 SD's from the median ... and somehow that doesn't look/feel/seem right to me.

Or at least, it isn't doing what I wanted to do, which was create some logical groups around upon the median -- because the values are pretty volatile
(high end last six vaules are $338,$550,$560,$621,$806,$836), I don't want 6 groups of 18 values each or whatever, I want groups that are "a lot below normal, a little below normal, about normal, a little above normal, and a lot above normal" ... but instead I'm getting a bunch of groups that are mostly varying degrees above normal".

So what am I doing wrong here: looking for the wrong thing? trying to find the right thing but via the wrong method? something else? or am I just trying to work when I'm unable to think clearly & properly, especially when I'm about 1.5 levels about my math ability & about 2/3rds of a level above my Excel skill?

Any help is appreciated, I'm tryin' to make a living over here.

[QuikSand]

-- the mean of the set is $12.68

That doesn't sound correct to me. You listed several individual values... 800, 500, 500, 300 etc -- that added up to at least a 3,000... and you said you only had 113 items. Sounds like your mean is a good deal higher than 13 to me.

I think that's a start...

[QuikSand]

I don't think there is any particular convention to follow with real outliers, like it sounds as though you have here. It is probably up to you whether to include them all in their own broad category, or whether they need to be broken down any further than that. As long as it's made fairly clear that they are uncommon occurrences in your list, I think that should be okay. Perhaps even use the term "outlier" in the description of the group you establish for them.

[Pumpy Tudors]

Just from the information you've given, I'm doubting that the standard deviation is nearly as high as you have described. Without having all of the data, I can't say that for sure, but if the SD is really that high, it's no wonder that you can't get the result you want.

[QuikSand]

Perhaps you have your Std Dev and Mean values reversed here?

[JonInMiddleGA]

Oops, I do see at least one mistake in what I posted
I said mean was $12.68 ... should have said the median is $12.68
The arithmetic mean (aka average) = $69.73

As for the standard deviation, I'll admit that I'm trusting Excel to do the computation on this, but ...

=STDEV(AB3:AB115)
is returning a value of 149.0253 (which I'm rounding to $149.03)

I've doublechecked to make sure that's the right range of cells (it is), so I can't be screwing up the SD unless I goofed something in the syntax of the formula ... right?

[QuikSand]

Yes, that sounds okay. Having a large tail on it to the high end (resulting from the few outliers) really messes up the spread. This is not a "normal" distribution where the sort of groupings you mention would make the most sense.

[JonInMiddleGA]

Okay, this will probably suck formatically, but here's the actual data from the column I'm working with.

$836.12
$806.96
$621.28
$560.50
$550.52
$338.42
$276.84
$272.20
$261.63
$236.02
$211.40
$192.18
$177.17
$165.88
$142.22
$122.45
$106.20
$106.06
$104.74
$93.94
$85.58
$82.48
$78.65
$72.17
$68.57
$68.13
$67.75
$62.74
$62.58
$59.25
$52.63
$51.08
$50.11
$45.17
$42.32
$36.03
$35.66
$32.03
$26.92
$26.58
$26.41
$25.81
$24.89
$23.40
$21.73
$21.09
$20.50
$19.71
$18.80
$18.68
$15.16
$14.99
$13.22
$13.19
$12.81
$12.74
$12.68
$12.57
$12.16
$11.85
$11.71
$11.08
$10.95
$10.76
$10.14
$9.92
$9.36
$8.49
$7.96
$7.48
$7.06
$6.69
$6.62
$6.58
$6.47
$6.16
$6.00
$5.72
$5.71
$5.08
$4.40
$4.28
$4.26
$3.97
$3.45
$3.45
$3.43
$3.39
$3.14
$3.14
$2.96
$2.93
$2.70
$2.58
$2.51
$2.50
$2.47
$2.44
$2.43
$2.38
$2.24
$2.14
$1.93
$1.80
$1.61
$1.27
$1.01
$0.89
$0.77
$0.72
$0.50
$0.32
$0.29

[JonInMiddleGA]

Yes, that sounds okay. Having a large tail on it to the high end (resulting from the few outliers) really messes up the spread. This is not a "normal" distribution where the sort of groupings you mention would make the most sense.

Hmm ... maybe there's an answer in here somewhere after all -- Believe it or not, I actually understand what I'm seeing in the data, at least as it relates to what I'm doing. By that I mean I understand what it tells me on a practical level, where it can be applied to some decision making.

My task, and the motivation for trying to create some sort of "groups", is to translate this back to the client, so that he'll understand it well enough to agree with my recommendations ;)

I was looking for something that would basically boil this down to "these markets* are 'Group A's', these markets are 'Group B's', ..." and so forth.

What the data is showing, and perhaps the round peg that I was trying to shove into a square hole, is that there's really a whole bunch of markets that are similar on the low end, a lot of markets clustered around the middle, a few at the top, and only a couple that are clearly head-and-shoulders above all the rest. I think I was trying to find a breakpoint that would let me combined those items highest on the list but the flat truth is, #6-10 really pale in comparison to #1-5.

I think what I'll end up doing (believing that each of about 9 other similar sets will present the same sort of range of results) is saying "Look, these few are clearly the top of the food chain", remove the high-end outliers from the mix & recalculate the SD -- that ought to give me more manageable groups of items with like values, which creates a set of "okay, these are the markets that we need to focus on with regard to our goals of X, Y, and Z"

So, even if I didn't end up where I was trying to go, I think I may have gotten on a path that will get me to a satisfactory place ... and being able to talk it through helped me do that. So thanks QS, and everybody else too.


*these numbers represent retail sales per 1,000 households in a given DMA.

[Pumpy Tudors]

Personally, I would treat the top five values as outliers and then make all calculations using the other values, as QuikSand appeared to allude to. I don't have access to Minitab here at home, but a boxplot of these values would be nice to look at right here. Anyway, since this is a nonnormal distribution, you're faced with two choices:

1. Use the median as the "normal" point, in which case the spread of "above normal" is going to be huge.

2. Use the mean as the "normal" point, but then you will have many more values "below normal" than "above normal."

I don't know what those values represent, so I don't know which option is better here, but I would normally be inclined to use option #2.

[QuikSand]

Personally, I would treat the top five values as outliers and then make all calculations using the other values, as QuikSand appeared to allude to.

I think that works as well as anything.

[Skolleck]

It really depends on what you WANT to show, you can use several ways to group these numbers.

One good way to show a spread to it use the Six Sigma Control Limits. However it depends on the spread from point to point being in time limits, not a decending data sample.

I will post the formula tomorrow, if you can post the numbers in actual order, then I will make the excel spreadsheet for you.


If you want more details, you are welcome to e-mail me at [email protected]

Thanks,
Scott Kolleck

[RPI-Fan]

Personally, I would treat the top five values as outliers and then make all calculations using the other values, as QuikSand appeared to allude to. I don't have access to Minitab here at home, but a boxplot of these values would be nice to look at right here. Anyway, since this is a nonnormal distribution, you're faced with two choices:

1. Use the median as the "normal" point, in which case the spread of "above normal" is going to be huge.

2. Use the mean as the "normal" point, but then you will have many more values "below normal" than "above normal."

I don't know what those values represent, so I don't know which option is better here, but I would normally be inclined to use option #2.

I have Minitab (Student Edition on my laptop)... if you can give me a day I'll run it tomorrow.

[JonInMiddleGA]

What the heck is "Minitab"?

[Crapshoot]

Stats package - but what you need can be run in Excel.