Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average of an array of entries, minus the lowest entry
I need the average of an array of values, minus the lowest value of those
entries. |
#2
|
|||
|
|||
= AVERAGE(A1:A10)-MIN(A1:A10)
Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. |
#3
|
|||
|
|||
I am reading this not as the average subtract the lowest value, but as the
average of all but the lowest value. If so, you can use =AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Mangesh Yadav" wrote in message ... = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. |
#4
|
|||
|
|||
And one more interpretation <bg:
=(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1) Bob Phillips wrote: I am reading this not as the average subtract the lowest value, but as the average of all but the lowest value. If so, you can use =AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Mangesh Yadav" wrote in message ... = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. -- Dave Peterson |
#5
|
|||
|
|||
Not so much an interpretation as an alternative <ebg
Bob "Dave Peterson" wrote in message ... And one more interpretation <bg: =(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1) Bob Phillips wrote: I am reading this not as the average subtract the lowest value, but as the average of all but the lowest value. If so, you can use =AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Mangesh Yadav" wrote in message ... = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. -- Dave Peterson |
#6
|
|||
|
|||
That may be what I need.
I have a 19 Megawatt power plant with 19 generators. If I have 9 units on line with an average of 0.8 Mw for each, what will the resulting average be if one unit trips off liine? I need to be sure I don't overload the remaining 8 units. Thank you, Sir. I'll try that and let you know how it works. Ingersollg "Mangesh Yadav" wrote: = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. |
#7
|
|||
|
|||
{=AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5))}
would behave differently than =(SUM(A1:A5)-MIN(A1:A5))/(COUNT(A1:A5)-1) when, for example: A1: 3 A2: 4 A3: 3 A4: 6 A5: 3 Bob Phillips wrote: Not so much an interpretation as an alternative <ebg Bob "Dave Peterson" wrote in message ... And one more interpretation <bg: =(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1) Bob Phillips wrote: I am reading this not as the average subtract the lowest value, but as the average of all but the lowest value. If so, you can use =AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Mangesh Yadav" wrote in message . .. = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. -- Dave Peterson |
#8
|
|||
|
|||
I have determined that it doesn't matter which cell in the array is dropped.
I used portions of two of the responses and came up with: =(SUM(B4:T4)/(COUNT(B4:T4)-1)) and it works exactly as I needed it to. Thanks to all who responded. Jerry Ingersoll "Bob Phillips" wrote: I am reading this not as the average subtract the lowest value, but as the average of all but the lowest value. If so, you can use =AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Mangesh Yadav" wrote in message ... = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. |
#9
|
|||
|
|||
Thanks. I used portions of two of the responses and came up with:
=(SUM(B4:T4)/(COUNT(B4:T4)-1)) which works great. Thanks for everyone's responses. Jerry Ingersoll "Dave Peterson" wrote: And one more interpretation <bg: =(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1) Bob Phillips wrote: I am reading this not as the average subtract the lowest value, but as the average of all but the lowest value. If so, you can use =AVERAGE(IF(A1:A5<MIN(A1:A5),A1:A5)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Mangesh Yadav" wrote in message ... = AVERAGE(A1:A10)-MIN(A1:A10) Mangesh "ingersollg" wrote in message ... I need the average of an array of values, minus the lowest value of those entries. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of column entries | Excel Discussion (Misc queries) | |||
How to validate data entries to be unique within an array | Excel Worksheet Functions | |||
Drop 3 Lowest Entries | Excel Worksheet Functions | |||
Finding the average by dropping the lowest | Excel Worksheet Functions | |||
average, array and offsets | Excel Worksheet Functions |