Statistics

[Don01]

Hello,

 

Just had a question regarding statistical analysis.

 

I'm trying to calculate the average of 4 numbers from a data set of 6 numbers in excel without manually choosing to average only the 4 numbers.

 

e.g.

 

 

85 20 32 45 27 3 (total mean = 35.3 desired mean = 31)

100 30 27 40 21 1 (total mean = 21.5 desired mean = 29.5)

....etc

 

The middle 4 numbers represent a more realistic result whilst the two end numbers are irrelevant.

 

I've tried looking into weighted averages however I'm unsure how to apply this to an excel sheet.

 

I'd prefer to find a formula which would discount numbers which vary wildly from the average amount and focus only on the few that don't.

 

Any help would be greatly appreciated.

[imatfaal]

my excel formulae are always super-clunky - but you could do it with nested if statements and a running total

 

=IF(B7<MAX($B$7:$B$12),(IF(B7>MIN($B$7:$B$12),B7+C6,C6)),C6)

 

That formula copied down column c will add as a running total all the figures in column b (in this instance from row 7 to 12) except the largest and the smallest. Then you can divide through by (count(B7:B12)-2) to get the average.

 

I did say my formulae were clunky

 

It will double discount if your largest or smallest number is repeated. I wouldnt be at all surprised if in one of the formula packs you can get a more powerful single formula. But I am no longer using excel (libre office yay!) so I cannot check

[ewmon]

Have you considered computing the mode or the median? I think the median might appeal to you.

 

The two end numbers are slightly relevant in that they give you the bounds inside which your "average" (however you choose to compute it) will definitely fall. This may seem trivial, but still, there it is, so they're not totally irrelevant.

 

You might like a central weighed method that I've often used as a moving average to eliminate raggedness in curves. I don't know the name of it, but I'm sure I didn't invent it. It works best with symmetrical populations, and less well for skewed populations.

 

Open your Microsoft Calculator (Click on: Start/All Programs/Accessories/Calculator). Type in 1001, and then click on x^y and then type in the number of your data points minus one (in this case it's 5). Your "answer" (for your weights) will be 1,005,010,010,005,001. Each thousand groupings will be your weighs, like this: 1, 5, 10, 10, 5, 1. In Excel, enter your data points in a column in the order of their magnitude. Your first example will be:

 

85

45

32

27

20

3

 

In the next column, enter your weights next to your data points, like this:

 

1

5

10

10

5

1

 

In the third column, set each cell equal to the data point times its weight (such as C1: =A1*B1, etc).

 

At the bottom of the third column (skip a line to make it easier to distinguish), sum your third column values (such as C8: =SUM(C1:C6)).

 

At the bottom of the second column (skip a line to make it easier to distinguish), sum your weights (such as B8: =SUM(B1:B6)). Surprise, surprise, this sum will be a power of 2. In this case, it's 32 (=2⁵). Where did you use 5 before? Aha!

 

At the bottom of the first column (skip a line to make it easier to distinguish), set the value equal to the bottom of the third column divided by the bottom of the second column — that is, the sum of the weighted data points divided by the sum of the weights — (such as A8: =C8/B8). This will be your weighted average.

 

For your two data sets, your weighted answers using this method should be: 31.34375 and 30.5. You wanted 31.0 and 29.5 (for some reason).

 

Why do I feel like I just did your homework?

[ecoli]

I recommend a box-and whisker plot showing the median and inter-quartile ranges. Outliers are shown or excluded but you still use all the data to calculate the median (the benefit of median, as ewmon suggests is that outliers don't skew the measurements.)