formula to throw out high number and low number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deb - 15 Sep 2007 18:48 GMT

I have 6 months of actuals and I need to throw out the month and the low
month then take the remain 4 months times 3 to come up with a years total

Reply to this Message

T. Valko - 15 Sep 2007 19:03 GMT

Try this:

=SUM(A1:A6,-MIN(A1:A6),-MAX(A1:A6))*3

Signature

Biff
Microsoft Excel MVP

>I have 6 months of actuals and I need to throw out the month and the low
> month then take the remain 4 months times 3 to come up with a years total

Reply to this Message

T. Valko - 15 Sep 2007 19:29 GMT

A few keystrokes shorter:

=SUM(SMALL(A1:A6,{2,3,4,5}))*3

Signature

Biff
Microsoft Excel MVP

> Try this:
>
> =SUM(A1:A6,-MIN(A1:A6),-MAX(A1:A6))*3
>
>>I have 6 months of actuals and I need to throw out the month and the low
>> month then take the remain 4 months times 3 to come up with a years total

Reply to this Message

Ron Coderre - 15 Sep 2007 20:02 GMT

Try this:
=TRIMMEAN(A1:A6,1/6)*12

TRIMMEAN (as used above) removes the highest and lowest values from the
range and averages the remaining values.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

>I have 6 months of actuals and I need to throw out the month and the low
> month then take the remain 4 months times 3 to come up with a years total

Reply to this Message

T. Valko - 15 Sep 2007 21:46 GMT

Typo:

> =TRIMMEAN(A1:A6,1/6)*12

=TRIMMEAN(A1:A6,2/6)*12

Signature

Biff
Microsoft Excel MVP

> Try this:
> =TRIMMEAN(A1:A6,1/6)*12
[quoted text clipped - 12 lines]
>>I have 6 months of actuals and I need to throw out the month and the low
>> month then take the remain 4 months times 3 to come up with a years total

Reply to this Message

Ron Coderre - 15 Sep 2007 22:01 GMT

Yup...it was 2 in my head...1 when I typed it (sheesh!)

Thanks

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> Typo:
>
[quoted text clipped - 19 lines]
>>> month then take the remain 4 months times 3 to come up with a years
>>> total

Reply to this Message

Stan Brown - 15 Sep 2007 21:25 GMT

Sat, 15 Sep 2007 10:48:01 -0700 from Deb
<Deb@discussions.microsoft.com>:

> I have 6 months of actuals and I need to throw out the month and the low
> month then take the remain 4 months times 3 to come up with a years total

Assuming they're in A1:A6,

=3*(sum(A1:A6)-min(A1:A6)-max(A1:A6))

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Reply to this Message

Balan - 16 Sep 2007 02:32 GMT

I think Deb wants to project the values for the remaining 12 months. The
statistical "Median" should take care of his needs better. than substracting
the min and max values and then multiplying the balance with 3:

=Median(a1:a6)*12

But the result will not be the same as substracting the min and max values
and then multiplying the balance with 3.

> Sat, 15 Sep 2007 10:48:01 -0700 from Deb
> <Deb@discussions.microsoft.com>:
[quoted text clipped - 4 lines]
>
> =3*(sum(A1:A6)-min(A1:A6)-max(A1:A6))

Reply to this Message

Stan Brown - 16 Sep 2007 06:51 GMT

Sat, 15 Sep 2007 18:32:00 -0700 from Balan
<Balan@discussions.microsoft.com>:

> > Sat, 15 Sep 2007 10:48:01 -0700 from Deb
> > <Deb@discussions.microsoft.com>:
[quoted text clipped - 4 lines]
> >
> > =3*(sum(A1:A6)-min(A1:A6)-max(A1:A6))

> I think Deb wants to project the values for the remaining 12 months. The
> statistical "Median" should take care of his needs better. than substracting
[quoted text clipped - 4 lines]
> But the result will not be the same as substracting the min and max values
> and then multiplying the balance with 3.

I agree with you that Deb's statistical procedure is questionable.

Signature

Reply to this Message