formula to throw out high number and low number
 

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

formula to throw out high number and low number
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Deb" wrote in message
...
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

formula to throw out high number and low number
 

A few keystrokes shorter:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"Deb" wrote in message
...
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

formula to throw out high number and low number
 

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)





"Deb" wrote in message
...
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

formula to throw out high number and low number
 

Sat, 15 Sep 2007 10:48:01 -0700 from Deb
:
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))

--
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/

formula to throw out high number and low number
 

Typo:

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

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

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)





"Deb" wrote in message
...
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

formula to throw out high number and low number
 

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

Thanks

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


"T. Valko" wrote in message
...
Typo:

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

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

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)





"Deb" wrote in message
...
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

formula to throw out high number and low number
 

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.


"Stan Brown" wrote:

Sat, 15 Sep 2007 10:48:01 -0700 from Deb
:
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))

--
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/

formula to throw out high number and low number
 

Sat, 15 Sep 2007 18:32:00 -0700 from Balan
:

"Stan Brown" wrote:

Sat, 15 Sep 2007 10:48:01 -0700 from Deb
:
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))

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.

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

--
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/