Average without multiple min and max values

[msquared99]

I have some data which has some MIN values way below the average and some MAX values way above the average. How do I eliminate the MIN and MAX values to get a "True" average?

Example:
150,000
110,000
90,000
20 rows between 15,000 - 25,000
5,000
1,200
1,000

How would I eliminate the 150,000;110,000;90,000;5,000;1,200;1,000 from my average?

Keep in mind that I have a list of several different customers, like in the uploaded file.

In the uploaded example 0069 would be a good example.

 

[Deepak]

Is something like as ..

=AVERAGEIF(A2:A402,"<>"&69,B2:B402)

Below are array so press Ctrl+Shift+Enter
=MIN(IF(A2:A402<>"0069",B2:B402))
=MAX(IF(A2:A402<>"0069",B2:B402))

 

[msquared99]

What I am looking to do is for 0069, eliminate the high end value of 18,302 and any value below 8,000 to get an average of 10,132 instead if 5,485.

And with 0024, eliminate the value of 156,003.

So, if there are values that are above or below what the normal average would be, then I want them to not calculate in the average.

I did use the MIN and MAX arrays but they only eliminate the highest value and the lowest value. In some cases I may have more than one value either high or low or both ways.

 

[Deepak]

hi msquared99,

What i got that is:

Your statements doesn't satisfying itself.

First, You said that eliminate the high & low end value to get a average.

Second, eliminating high end values.

Third, Get a average of values > lowend & <highend.

So, need your kind attention to eliminate the confusion.

 

[vletm]

@msquared99
what about this?

You can set Your own value to Cell[D2]