Average all but the lowest/highest numbers? Possible? 2

[TulsaJeff]

As an Industrial Engineer... I have created a worksheet in excel which will allow me to do automatic calculations with time standards. I will take 3 to 5 readings on a particular task (5 normally). The way I normally handle this is to throw out the lowest and the highest numbers and average the rest...that would be my standard time on a given task.

My question... is their a way for excel to look at say 5 numbers and automatically average all but the lowest and the highest number? Is this asking too much?

I would like to do this without getting into any code if possible but if that is the only way then by all means let me know what the code is.

Thanks for all the help. Ya' Gotta Love It!

 

[SimonDavis]

I can suggest how to do it in several stages, using some standard functions - I think this works, but check it!

First you can use the 'count' function to see how many numbers are in the series: = count(rangename)

Then you can use the min and max to find the highest and lowest: =min(rangename) or max(rangename)

If you then get the sum for the whole range, deduct the sum of the min and max, and divide the result by count-2, I believe that will give you the right answer.

 

[Goska]

Well, the fastest way I can think of is messy.

Data is in the range a1:a:5

=AVERAGE(SMALL(A1:A5,2),SMALL(A1:A5,3),SMALL(A1:A5,4))

Which will work for these small data sets

Or for a larger data set, put your list in column A and nothing else. This will only kick out the top and bottom vaules

B1=sum(a:a)
B2=max(a:a)
B4=min(a:a)
B4=count(a:a)
B5=the answer=(B1-B2-B3)/B4

Hope this helps

 

[Goska]

Something to add to mine that I forgot about. SimonDavis's post reminded me of this. We both posted almost at the same time.

b4=count(a:a)-2

Sorry about that.

 

[TulsaJeff]

guys ...I believe that will work. You guys are Great! Ya' Gotta Love It!