Average a list with high and low ignored

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

"Pete_UK" wrote:

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

On Feb 6, 5:16 pm, Shu of AZ
wrote:
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" wrote:
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

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

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:
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(D4150,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- Hide quoted text -

- Show quoted text -

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4: D15))

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


"Pete_UK" wrote in message
...
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

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

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:
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(D4150,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

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200767


"Shu of AZ" wrote:

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

"Gary''s Student" wrote:

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)

--
Gary''s Student - gsnu200767


"Shu of AZ" wrote:

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(D4150,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

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

"Shu of AZ" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200767


"Shu of AZ" wrote:

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

"Gary''s Student" wrote:

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)

--
Gary''s Student - gsnu200767


"Shu of AZ" wrote:

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(D4150,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

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200767


"Shu of AZ" wrote:

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

"Gary''s Student" wrote:

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)

--
Gary''s Student - gsnu200767


"Shu of AZ" wrote:

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(D4150,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

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

=AVERAGE(IF((D4150)*(D415X1)*(D415Y1),D4: D15))

entered with CSE.

Pete

On Feb 6, 5:53*pm, "T. Valko" wrote:
=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4: D15))

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

"Pete_UK" wrote in message

...
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

whe
X1: * =MAX(D415)
Y1: * =MIN(D415)

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:



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(D4150,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- Hide quoted text -

- Show quoted text -

That'll work.

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Okay, thanks for pointing that out Biff - will this do?:

=AVERAGE(IF((D4150)*(D415X1)*(D415Y1),D4: D15))

entered with CSE.

Pete

On Feb 6, 5:53 pm, "T. Valko" wrote:
=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4: D15))

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

"Pete_UK" wrote in message

...
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

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

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:



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(D4150,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- Hide quoted text -

- Show quoted text -

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" wrote:

That'll work.

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Okay, thanks for pointing that out Biff - will this do?:

=AVERAGE(IF((D4150)*(D415X1)*(D415Y1),D4: D15))

entered with CSE.

Pete

On Feb 6, 5:53 pm, "T. Valko" wrote:
=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4: D15))

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

"Pete_UK" wrote in message

...
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

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

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:



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(D4150,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- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:

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

=AVERAGE(IF((D4150)*(D415X1)*(D415Y1),D4: D15))

entered with CSE.

Pete

On Feb 6, 5:53 pm, "T. Valko" wrote:
=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4: D15))

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

"Pete_UK" wrote in message

...
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

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

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:



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(D4150,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- Hide quoted text -

- Show quoted text -

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

--
Biff
Microsoft Excel MVP


"Shu of AZ" wrote in message
...
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" wrote:

That'll work.

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Okay, thanks for pointing that out Biff - will this do?:

=AVERAGE(IF((D4150)*(D415X1)*(D415Y1),D4: D15))

entered with CSE.

Pete

On Feb 6, 5:53 pm, "T. Valko" wrote:
=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4: D15))

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

"Pete_UK" wrote in message

...
You could try this variation:

=AVERAGE(IF(AND(D4150,D415X1,D415Y1),D4 15))

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

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

Hope this helps.

Pete

On Feb 6, 4:49 pm, Shu of AZ
wrote:



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(D4150,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- Hide quoted text -

- Show quoted text -