Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I extract 3 middle results and average them in excel
A scoresheet in excel returns 5 separate scores. I need to ignore the
highest and the lowest figures and average the other three to get a result. How do I do this. I have tried =IF(H71<B74,B75,B76,B77, IF(H71B74,B75,B76,B77,"0","H71") h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST APPRECIATED. |
#2
|
|||
|
|||
One way ..
Put in H71: =1/3*(SUM(B73:B77)-MAX(B73:B77)-MIN(B73:B77)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "SYBS" wrote in message ... A scoresheet in excel returns 5 separate scores. I need to ignore the highest and the lowest figures and average the other three to get a result. How do I do this. I have tried =IF(H71<B74,B75,B76,B77, IF(H71B74,B75,B76,B77,"0","H71") h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST APPRECIATED. |
#3
|
|||
|
|||
Try...
=AVERAGE(LARGE(B73:B77,{2,3,4})) Hope this helps! In article , "SYBS" wrote: A scoresheet in excel returns 5 separate scores. I need to ignore the highest and the lowest figures and average the other three to get a result. How do I do this. I have tried =IF(H71<B74,B75,B76,B77, IF(H71B74,B75,B76,B77,"0","H71") h71 IS THE CELL CONTAINING THE SCORE WHICH SHOWS IN B73. THE OTHER FOUR SCORES ARE ALL SHOWN IN B74, B75,B76 AND B77. i ONLY WANT THE MIDDLE THREE SCORES TO BE AVERAGED AND PRODUCE A RESULT. HELP WOULD BE MOST APPRECIATED. |
#4
|
|||
|
|||
Or, even <g: =AVERAGE(SMALL(B73:B77,{2,3,4}))
which produces the same results -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
Another way
=TRIMMEAN(B73:B77,2/COUNT(B73:B77)) -- Regards, Peo Sjoblom (No private emails please) "Max" wrote in message ... Or, even <g: =AVERAGE(SMALL(B73:B77,{2,3,4})) which produces the same results -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you AVERAGE IF and not null? | Excel Worksheet Functions | |||
Avearging columns with less than (<) text entries | Excel Worksheet Functions | |||
Calculate Years/Months Between Dates and then Average | Excel Discussion (Misc queries) | |||
IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
Count and then average | Excel Worksheet Functions |