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.

Try changing the {1,2,3,4,5,6} in the middle to {2,3,4,5}

Hope this helps.

Pete

On Apr 16, 4:51*pm, David wrote:
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.

Try...

=TRIMMEAN(INDEX(B2:J2,LARGE(IF(B2:J2<"",COLUMN(B2 :J2)-COLUMN(B2)+1),6)):
J2,2/6)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
David wrote:

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.

Domenic, thank you very much. Your formula appears to working like a charm.
Thanks again.
David

"Domenic" wrote:

Try...

=TRIMMEAN(INDEX(B2:J2,LARGE(IF(B2:J2<"",COLUMN(B2 :J2)-COLUMN(B2)+1),6)):
J2,2/6)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
David wrote:

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.

Domenic, this formula works great for the information in cells b2:j2. I try
to extend it and I receive an error message that there is the wrong kind of
data in the cells. Our league is for old men who play golf every Mon, Wed,
and Friday for about 6 or 7 months depending upon the weather. I need to
extend this formula for for a total of 100 cells or b2:cw2. Can this be done?
Thanks again
David

"Domenic" wrote:

Try...

=TRIMMEAN(INDEX(B2:J2,LARGE(IF(B2:J2<"",COLUMN(B2 :J2)-COLUMN(B2)+1),6)):
J2,2/6)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
David wrote:

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.