Golf league average score

Hi,

I run a golf league and have a score sheet in excel to help figure out our handicaps.
I am looking for a function or formula that will look back at the last 6 weeks scores (not counting the zero's OR blanks) and drop the highest 2, then calculate an average of the other 4.

Example: (also sample data file attached)

A B C D E F G H i J K L M N O P Q R S
Mike 44 41 43 39 43 0 44 45 41 46 41

If we were to drop weeks I and K, the average score would be 43+44+41+41=169 169/4=42.25

We golf 18 weeks, so I'd like the formula to include all 18 weeks so I don't have to keep adjusting the formula each week.

Thanks in advance!
Mike

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.
http://www.excelforum.com/excel-form...age-score.html
I will close this 1, I can see what you were trying to do in the other 1