Average formula

[still learning]

Hi
How would I write a formula to fine an average for a a column that excludes the highest and lowest value.
=average( A4:A200.......not to include the highest or lowest value)

mike

 

[Peter_SSs]

What should happen if there are two or more equal highest (or lowest) values in the range? Should the average exclude all the equal ones or just one of them?

 

[still learning]

Hi Pete
Happy labor day
exclude all the equal ones

mike

 

[Peter_SSs]

exclude all the equal ones

In that case, try

=AVERAGEIFS(A4:A200,A4:A200,"<>"&MAX(A4:A200),A4:A200,"<>"&MIN(A4:A200))

 

[Peter_SSs]

Another option would be

=AVERAGE(FILTER(A4:A200,(A4:A200<>MAX(A4:A200))*(A4:A200<>MIN(A4:A200))))

 

[still learning]

Thank you....Peter


mike

 

[Peter_SSs]

You're welcome.

BTW, I'm not sure if you would be using multiple formulas like this but I have just done some testing with those two formulas and the AVERAGEIFS version seems to be considerably quicker to calculate.
Unless you are using a lot of similar formulas, you would probably never notice the difference though.