I need to find the median 3 values in a range, which will be added together.
I will have either 3, 5, or 7 values in the range. If I have 3 values, I just
need to add them; with 5 values, I need to throw out the high and low values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two of the
7s)

Can anyone help with this?
Thanks,
~ Horatio

=sum(range)-choose((count(range)-1)/2,0,min(range)+max(range),small(range,1)+small(ran ge,2)+large(range,1)+large(range,2))
"Horatio J. Bilge, Jr." wrote:

I need to find the median 3 values in a range, which will be added together.
I will have either 3, 5, or 7 values in the range. If I have 3 values, I just
need to add them; with 5 values, I need to throw out the high and low values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two of the
7s)

Can anyone help with this?
Thanks,
~ Horatio

That worked great. Thanks.


"Bob Umlas, Excel MVP" wrote:

=sum(range)-choose((count(range)-1)/2,0,min(range)+max(range),small(range,1)+small(ran ge,2)+large(range,1)+large(range,2))
"Horatio J. Bilge, Jr." wrote:

I need to find the median 3 values in a range, which will be added together.
I will have either 3, 5, or 7 values in the range. If I have 3 values, I just
need to add them; with 5 values, I need to throw out the high and low values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two of the
7s)

Can anyone help with this?
Thanks,
~ Horatio

On Mon, 19 Nov 2007 07:29:02 -0800, Horatio J. Bilge, Jr.
wrote:

I need to find the median 3 values in a range, which will be added together.
I will have either 3, 5, or 7 values in the range. If I have 3 values, I just
need to add them; with 5 values, I need to throw out the high and low values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two of the
7s)

Can anyone help with this?
Thanks,
~ Horatio

=SUMPRODUCT(LARGE(rng,ROW(INDIRECT(1+INT((COUNT(rn g)-1)/3)&":"&COUNT(rng)-INT((COUNT(rng)-1)/3)))))


--ron

Assuming there is always only 3, 5 or 7 numbers:

=TRIMMEAN(rng,(COUNT(rng)-3)/COUNT(rng))*3

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
I need to find the median 3 values in a range, which will be added
together.
I will have either 3, 5, or 7 values in the range. If I have 3 values, I
just
need to add them; with 5 values, I need to throw out the high and low
values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two of
the
7s)

Can anyone help with this?
Thanks,
~ Horatio

That's a slick method. Thanks.


"T. Valko" wrote:

Assuming there is always only 3, 5 or 7 numbers:

=TRIMMEAN(rng,(COUNT(rng)-3)/COUNT(rng))*3

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
I need to find the median 3 values in a range, which will be added
together.
I will have either 3, 5, or 7 values in the range. If I have 3 values, I
just
need to add them; with 5 values, I need to throw out the high and low
values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two of
the
7s)

Can anyone help with this?
Thanks,
~ Horatio

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
That's a slick method. Thanks.


"T. Valko" wrote:

Assuming there is always only 3, 5 or 7 numbers:

=TRIMMEAN(rng,(COUNT(rng)-3)/COUNT(rng))*3

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote
in
message ...
I need to find the median 3 values in a range, which will be added
together.
I will have either 3, 5, or 7 values in the range. If I have 3 values,
I
just
need to add them; with 5 values, I need to throw out the high and low
values;
and with 7 values, I need to throw out the 2 high and 2 low values.

For example:
Range: 3, 5, 5, 6, 4 Result: 5+5+4 (throw out 3 and 6)
Range: 5, 7, 6, 5, 7, 7, 6 Result: 7+6+6 (throw out both 5s and two
of
the
7s)

Can anyone help with this?
Thanks,
~ Horatio