golf handicap calculation

[leep75]

I have an excel problem. I have a golf league formula that takes the best 5 scores out of the last 6 and calculates a handicap based on 90% of the average.....Howver, my problem is if a player has not played 6 weeks a differet forumula is used. How would I do that using one formula. Could I incorporate several if/then statements into one monster formula? Here's the formula I am using if the player has 6 rounds completed:

=(AVERAGE(SMALL(B2:G2,{1,2,3,4,5}))-36)*0.9

Here is what is used if they haven't:

5 scores - Used 5 scores subtracted 180 divided by 5 and took 90%
4 scores - Used 4 scores subtracted 144 divided by 4 and took 85%
3 scores - Used 3 scores subtracted 108 divided by 3 and took 80%
2 scores - Used 2 scores subtracted 72 divided by 2 and took 75%
1 score - Used 1 score subtracted 36 and took 70%

[daddylonglegs]

Would this do it?

=((SUM(B2:G2)-MAX(B2:G2)*(COUNT(B2:G2)=6))/MIN(COUNT(B2:G2),5)-36)*(0.65+MIN(COUNT(B2:G2),5)/20)

[leep75]

Would this do it?

=((SUM(B2:G2)-MAX(B2:G2)*(COUNT(B2:G2)=6))/MIN(COUNT(B2:G2),5)-36)*(0.65+MIN(COUNT(B2:G2),5)/20)

WOW! I think it does. That's incredible. Can you explain how it works? Here's another thought. In this workbook I am thinking about 3 sheets. Rounds, Scores, and Handicaps. I'd like to be able to input each players weekly scores into the "Scores" sheet. Then that number would then be auto input into the "Rounds" sheet in the next available slot (only keeping 6 rounds per player). Then the "Handicaps" sheet would reference those 6 scores on the "rounds' sheet to figure the players handicap.

Does that make sense and is it do-able?