| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
| change the color of a number when it gets too high | Excel Discussion (Misc queries) | |||
| countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
| How do I set up a formula to throw out the 2 lowest scores? | Excel Worksheet Functions | |||
| formula that gives me a random number less than other number | Excel Worksheet Functions | |||
Linear Mode
