Average of an array of entries, minus the lowest entry
 

I need the average of an array of values, minus the lowest value of those
entries.

= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of those
entries.

I am reading this not as the average subtract the lowest value, but as the
average of all but the lowest value. If so, you can use

=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Mangesh Yadav" wrote in message
...
= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of
those
entries.

And one more interpretation <bg:
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1)



Bob Phillips wrote:

I am reading this not as the average subtract the lowest value, but as the
average of all but the lowest value. If so, you can use

=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Mangesh Yadav" wrote in message
...
= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of
those
entries.



--

Dave Peterson

Not so much an interpretation as an alternative <ebg

Bob

"Dave Peterson" wrote in message
...
And one more interpretation <bg:
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1)



Bob Phillips wrote:

I am reading this not as the average subtract the lowest value, but as
the
average of all but the lowest value. If so, you can use

=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Mangesh Yadav" wrote in message
...
= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of
those
entries.



--

Dave Peterson

That may be what I need.
I have a 19 Megawatt power plant with 19 generators. If I have 9 units on
line with an average of 0.8 Mw for each, what will the resulting average be
if one unit trips off liine? I need to be sure I don't overload the remaining
8 units.
Thank you, Sir. I'll try that and let you know how it works.
Ingersollg

"Mangesh Yadav" wrote:

= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of those
entries.

{=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))}

would behave differently than

=(SUM(A1:A5)-MIN(A1:A5))/(COUNT(A1:A5)-1)

when, for example:

A1: 3
A2: 4
A3: 3
A4: 6
A5: 3

Bob Phillips wrote:
Not so much an interpretation as an alternative <ebg

Bob

"Dave Peterson" wrote in message
...

And one more interpretation <bg:
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1)



Bob Phillips wrote:

I am reading this not as the average subtract the lowest value, but as

the

average of all but the lowest value. If so, you can use

=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Mangesh Yadav" wrote in message
. ..

= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...

I need the average of an array of values, minus the lowest value of

those

entries.


--

Dave Peterson

I have determined that it doesn't matter which cell in the array is dropped.
I used portions of two of the responses and came up with:

=(SUM(B4:T4)/(COUNT(B4:T4)-1))

and it works exactly as I needed it to. Thanks to all who responded.

Jerry Ingersoll

"Bob Phillips" wrote:

I am reading this not as the average subtract the lowest value, but as the
average of all but the lowest value. If so, you can use

=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Mangesh Yadav" wrote in message
...
= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of
those
entries.

Thanks. I used portions of two of the responses and came up with:

=(SUM(B4:T4)/(COUNT(B4:T4)-1))

which works great.

Thanks for everyone's responses.

Jerry Ingersoll

"Dave Peterson" wrote:

And one more interpretation <bg:
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1)



Bob Phillips wrote:

I am reading this not as the average subtract the lowest value, but as the
average of all but the lowest value. If so, you can use

=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"Mangesh Yadav" wrote in message
...
= AVERAGE(A1:A10)-MIN(A1:A10)

Mangesh



"ingersollg" wrote in message
...
I need the average of an array of values, minus the lowest value of
those
entries.



--

Dave Peterson