I am running a small golf league and need help with a function to calculate handicaps.
The criteria is to take the last 6 score differentials, drop the 2 highest and then take the average of the 4 lowest then multiply the result by .95. Here's the formula I am using.
{=AVERAGE(SMALL(IF(C9:H9<>0,C9:H9),ROW(INDIRECT("1:"&MIN(4,COUNTIF(C9:H9,">0"))))))*0.95} See cell H18 in the attached.
The formula works fine except when a player is absent and a "0" is entered for his differential. The problem is that I am using a defined range that only looks back at the last 6 cells. I need the formula to keep looking back until it finds the last 6 cells excluding ones that are 0 and then get the average etc. I can't figure out how to get the formula to look back until it finds the lowest 4 of 6 differentials that are <>0.
Any help would be appreciated.
Thanks,
Bob
Bookmarks