Statistics - Standard Deviation, Standard Error and Mean

[Learn]

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
...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.

 

[Simon Bridge]

You have six numbers - if you picked four of them at random, what would be their average?
That about it?

Clearly that depends on the four numbers - but you can work out each possible combination of 4, and work out the average for each one. The expectation value of the averages will be your answer.

 

[DrDu]

A better strategy than excluding observations arbitrarily would be to compute "robust" estimates of the distribution, e.g. the median instead of the mean and the median absolute deviation (MAD) as an estimator for the variability.
See
http://en.wikipedia.org/wiki/Robust_statistics

 

[Bacle2]

As a general comment, to reinfoce what was already said, in distributions that are not symmetrical or not unimodal, and or distributions with outliers, the variance is not a good measure of spread. You may also want to represent your data with a dot plot or stemplot:

http://en.wikipedia.org/wiki/Stem-and-leaf_plot

 

[blue_raver22]

Hello,

Thank you for your question about statistical analysis. To calculate the average of only 4 numbers from a data set of 6, you can use the AVERAGEIF function in Excel. This function allows you to specify a criteria for which numbers to include in the average calculation. In this case, you can use the criteria "greater than 10" to exclude the two end numbers that are irrelevant. The formula would look like this: =AVERAGEIF(A1:A6,">10"). This will give you the average of the four numbers in the range A1:A6 that are greater than 10.

In terms of discounting numbers that vary wildly from the average, you can use the standard deviation and standard error to determine the amount of variation in the data set. The standard deviation measures the spread of the data points from the mean, while the standard error measures the accuracy of the mean. If you have a large standard deviation and a small standard error, it may indicate that there are outliers in the data that are affecting the average. In this case, you may want to consider removing those outliers or using a different measure of central tendency, such as the median.

I hope this helps and please let me know if you have any further questions. Best of luck with your analysis!