| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging without the highest or lowest values
I've tried Excel help and played around with formulas, and I can't find
anything for what should be a pretty simple issue: I want to calculate averages on a series of values for each month, while discarding the highest and lowest values. Perhaps I just didn't word it right when using Excel help, but no results were helpful ... Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging without the highest or lowest values
=(SUM(A1:A100)-MAX(A1:A100)-MIN(A1:A100))/98
-- Gary''s Student - gsnu200753 "leadfoot" wrote: I've tried Excel help and played around with formulas, and I can't find anything for what should be a pretty simple issue: I want to calculate averages on a series of values for each month, while discarding the highest and lowest values. Perhaps I just didn't word it right when using Excel help, but no results were helpful ... Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging without the highest or lowest values
=AVERAGE(IF((A1:A10<MAX(A1:A10))*(A1:A10<MIN(A1: A10)),A1:A10))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. This will not work if there is more than one of the MAX/MIN. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "leadfoot" wrote in message ... I've tried Excel help and played around with formulas, and I can't find anything for what should be a pretty simple issue: I want to calculate averages on a series of values for each month, while discarding the highest and lowest values. Perhaps I just didn't word it right when using Excel help, but no results were helpful ... Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging without the highest or lowest values
=TRIMMEAN(A1:A10,0.2)
-- Regards, Peo Sjoblom "leadfoot" wrote in message ... I've tried Excel help and played around with formulas, and I can't find anything for what should be a pretty simple issue: I want to calculate averages on a series of values for each month, while discarding the highest and lowest values. Perhaps I just didn't word it right when using Excel help, but no results were helpful ... Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging without the highest or lowest values
On Nov 1, 10:29 am, leadfoot
wrote: I've tried Excel help and played around with formulas, and I can't find anything for what should be a pretty simple issue: I want to calculate averages on a series of values for each month, while discarding the highest and lowest values. Perhaps I just didn't word it right when using Excel help, but no results were helpful ... Thanks! Try... =TRIMMEAN(Range,2/COUNT(Range)) Hope this helps! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference Between Highest And Lowest? | Excel Discussion (Misc queries) | |||
| Average of values in row excluding the highest and lowest value? | Excel Worksheet Functions | |||
| Formula to higlight highest and lowest values in row | Excel Worksheet Functions | |||
| Graph Highest to Lowest. | Excel Discussion (Misc queries) | |||
| Highest High and Lowest Low | Excel Discussion (Misc queries) | |||
Linear Mode
