Our golf league uses a point system not a regular handicap system. The
points per round are entered on a worksheet - worksheet 1. Dates are across
the top and player's names down colunm a. Data would begin in column b and
row 2. There will be many days a player can not play. We take the last 6
scores, throw out the highest score and the lowest score and average the
remaining 4 scores. On worksheet 2 we list the same players and dates and
run a current goal which is the same the average of the four scores. I have
this array formula:
=sumproduct(isnumber(match(columnb2:j2),large(((b2 :j2<0*column(b2:j2)),{1,2,3,4,5,6}),0))*b2:j2)/min(6,sumproduct(--(b2:j2<0)))
This formula averages the last 6 non blank cells but fails to throw out the
highest and lowest scores. If anyone can help, it would be greatly
appreciated. Thank you.