Eliminating some data from averageif calculation

Hello, I currently have a sheet where a formula delivers the largest 3 numbers sorted in order from a maximum 6 rows of data.

I then run another formula that averages that returned data - eg the average of the 3 highest values.

=AVERAGEIF(AJ4:AJ6,"<>0")

Sometimes I get some anomalies where the original 3 returned numbers will not be as close to each other in values (as they should be).

What I would like to be able to do it to eliminate any number that is, say .50 less than the largest number, and then average the remaining.

For example, if the original returned numbers were 20.69, 20.49, 20.09 - I would like to eliminate the 20.09 and then calculate the average of the other 2 numbers.

Is there something that I can add to my existing formula - =AVERAGEIF(AJ4:AJ6,"<>0") - that will eliminate anything more than .5 less than the highest number in the range and then calculate the average?

Many thanks

Gary

If you need more arguments, take a look at averageifS() instead

Try this:

=IF(MAX($AJ$4:$AJ$6)-MIN($AJ$4:$AJ$6)>0.5,AVERAGEIF($AJ$4:$AJ$6,">"&MIN($AJ$4:$AJ$6)),AVERAGEIF($AJ$4:$AJ$6,"<>0"))

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.