Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average (exclude 1 min & 1 max Value)
Excel2003 ...
Range D5:R5 contains positive numbers (not sorted) & some numbers may repeat. I wish to AVG range (exclude 1 Min & 1 Max Value) ... In the list below I wish to AVG (exclude 1 min value ( 24 ) & 1 max value ( 55 ). 25-26-42-53-24-32-45-35-43-24-55-43-24-55-43-43-45-46-36-34 Thanks ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average (exclude 1 min & 1 max Value)
one way:
=(sum(d5:r5)-min(d5:r5)-max(d5:r5)))/(count(d5:r5)-2) another: =TRIMMEAN(d5:r5,(COUNT(d5:r5)-2)/COUNT(d5:r5)) Ken wrote: Excel2003 ... Range D5:R5 contains positive numbers (not sorted) & some numbers may repeat. I wish to AVG range (exclude 1 Min & 1 Max Value) ... In the list below I wish to AVG (exclude 1 min value ( 24 ) & 1 max value ( 55 ). 25-26-42-53-24-32-45-35-43-24-55-43-24-55-43-43-45-46-36-34 Thanks ... Kha -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average (exclude 1 min & 1 max Value)
Good morning Dave ...
Your 1st solution is the one I came up with ... but it took me awhile to figure out the placement of the parenthesies. Above said ... Thank you for supporting these boards ... Kha "Dave Peterson" wrote: one way: =(sum(d5:r5)-min(d5:r5)-max(d5:r5)))/(count(d5:r5)-2) another: =TRIMMEAN(d5:r5,(COUNT(d5:r5)-2)/COUNT(d5:r5)) Ken wrote: Excel2003 ... Range D5:R5 contains positive numbers (not sorted) & some numbers may repeat. I wish to AVG range (exclude 1 Min & 1 Max Value) ... In the list below I wish to AVG (exclude 1 min value ( 24 ) & 1 max value ( 55 ). 25-26-42-53-24-32-45-35-43-24-55-43-24-55-43-43-45-46-36-34 Thanks ... Kha -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average non-adjacent cells & exclude zeros | Excel Discussion (Misc queries) | |||
Exclude zero values using average function in a pivot table | Excel Discussion (Misc queries) | |||
How do I exclude a cell from an average calculation when the cell. | Excel Discussion (Misc queries) | |||
How to average a column, but exclude zero AND negative values? | Excel Discussion (Misc queries) | |||
Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions |