Average a list with high and low ignored

[Shu of AZ]

In the example below, there are 8 of a possible 10 numbers. The amount
varies but never more than 10. These numbers represent a value of an
equation earlier on in the worksheet. I need to be able to formulate the
average of these numbers after the high and low has been removed. In this
case, 52.28 would not be used nor would 50.81. I currently use
{=AVERAGE(IF(D415>0,D415))} to do the average of all but need to remove
the high and low automatically. Can anyone assist in this? Thanks in
advance.

51.32
51.54
51.74
50.93
52.28
51.07
51.85
50.81
Blank
Blank
= Average with the high and low taken out

 

[Gary''s Student]

If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so:

=(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2)

 

[Dave Peterson]

One way:

=trimmean(a1:a10,2/count(a1:a10))

another:

=(sum(a1:a10)-min(a1:a10)-max(a1:a10))/(count(a1:a10)-2)

 

[Pete_UK]

You could try this variation:

=AVERAGE(IF(AND(D415>0,D415<X1,D415>Y1),D415))

where:
X1: =MAX(D415)
Y1: =MIN(D415)

By the way, doesn't you range cover more than 10 numbers?

Hope this helps.

Pete

 

[Shu of AZ]

thank you very much

If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so:

=(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2)

 

[Shu of AZ]

I wanted to ask something just for my own knowledge. Why the -2 at the end
of the formula?

 

[Shu of AZ]

Thank you Dave

One way:

=trimmean(a1:a10,2/count(a1:a10))

another:

=(sum(a1:a10)-min(a1:a10)-max(a1:a10))/(count(a1:a10)-2)

 

[Shu of AZ]

Thank you. You are correct in the range of the formula I use. I normally
would have a possible 12 but the criteria is changing to 10 and I failed to
note that in the message. Good catch!

 

[Pete_UK]

I didn't point out that the AVERAGE/IF formula is an array formula, so
you will have to commit with CTRL-SHIFT-ENTER.

Also, I see that you've started a new thread with the same question
over in .misc.

Pete

 

[Gary''s Student]

We need the -2 because we must divide by the number of samples we are
actually using. For example, if A1 thru A10 had two blank cells, then
COUNT(A1:A10) gets us down to 8 (REMEMBER: AVERAGE() ignores blanks!)


We are also discarding the high & low value, so we must get down to 6.
Therefore the -2

 

[Shu of AZ]

I noticed that and intended to send it to worksheet functions first

 

[T. Valko]

=AVERAGE(IF(AND(D415>0,D415<X1,D415>Y1),D415))

That won't work. AND doesn't return an array, it returns a single value,
either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this
case it will *always* return FALSE because at least one value will never be
less than the MAX and one value will never be greater than the MIN value.


--
Biff
Microsoft Excel MVP


You could try this variation:

=AVERAGE(IF(AND(D415>0,D415<X1,D415>Y1),D415))

where:
X1: =MAX(D415)
Y1: =MIN(D415)

By the way, doesn't you range cover more than 10 numbers?

Hope this helps.

Pete

 

[Shu of AZ]

I've run into a snag. One of my data sets contained only 2 values of the 10
possible and returned an ISERROR. I suppose it would also do that for 3
values if that was the case.

 

[Shu of AZ]

I also had one just come in with 1 and it returned the value contained in the 1

 

[Shu of AZ]

I found it only occurs with 2 values. Would an if statement work somehow?

 

[Pete_UK]

Okay, thanks for pointing that out Biff - will this do?:

=AVERAGE(IF((D415>0)*(D415<X1)*(D415>Y1),D415))

entered with CSE.

Pete

 

[T. Valko]

That'll work.

--
Biff
Microsoft Excel MVP


Okay, thanks for pointing that out Biff - will this do?:

=AVERAGE(IF((D415>0)*(D415<X1)*(D415>Y1),D415))

entered with CSE.

Pete

 

[Shu of AZ]

would you both take a look at my response to Garys answer to my question. It
seemed to work great but hits a snag when there are only 2 values. Gary
might be off line and didn't see my reply. THANKS

 

[Shu of AZ]

would you both take a look at my response to Garys answer to my question. It
seemed to work great but hits a snag when there are only 2 values. Gary
might be off line and didn't see my reply. THANKS

 

[T. Valko]

In order to drop the lowest and highest values you need at least 3 numbers
in your range.

Define *exactly* what should happen if:

There are *no* values at all (if that's even possible)
There are less than 3 values