Software

Ask something
Discussion Locked

Averaging in excel

0 helpful
1 reply

by dhamilt0

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need a formula to average a array of numbers and to take out a certain amount of highs and lows for a more accurate average. For example, 12345 take out the 1 and 5. Similar to standard deviation.

1 reply

by dklippert

Here are a few ways
"TRIMMEAN:
Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis."

=TRIMMEAN(A1:A20, 2.001/COUNT(A1:A20))

=SUM(A1:A20,-(MAX(A1:A20)+MIN(A1:A20)))/(COUNT(A1:A20)-2)

Arrays:

{=AVERAGE(LARGE(A1:A20,ROW(INDIRECT("1:20"))))}

{=AVERAGE(IF((A1:A20<>MAX(A1:A20))*(A1:A20<>MIN(A1:A20)),A1:A20,""))}

This discussion is closed to new replies.