formula to throw out high number and low number

[Guest]

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

 

[T. Valko]

Try this:

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

 

[T. Valko]

A few keystrokes shorter:

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

 

[Ron Coderre]

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)

 

[Stan Brown]

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

 

[T. Valko]

Typo:

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

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

 

[Ron Coderre]

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

Thanks

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

 

[Guest]

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]

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

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.