Average of last 3 non-0 numbers in row?
 

I am secratary of golf league and handicap is calculated of the average of
the last 3 rounds played. If a player is absent for a week, there is a zero
in his score. This would be a rolling average of coarse.

Average of last 3 non-0 numbers in row?
 

=AVERAGE(N(OFFSET($B$2,0,LARGE(IF($B$2:I2<0,COLUM N($B$2:I2)-MIN(COLUMN($B$2:I2))),{1,2,3}))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"joewis" wrote in message
...
I am secratary of golf league and handicap is calculated of the average of
the last 3 rounds played. If a player is absent for a week, there is a
zero
in his score. This would be a rolling average of coarse.

Average of last 3 non-0 numbers in row?
 

With....Positive scores (or zeros) in B2:K2

This regular formula calculates the average of the last 3 values that are
greater than zero:
L2:
=AVERAGE(LOOKUP(LARGE(INDEX(ISNUMBER(B2:K2)*(B2:K2 0)*COLUMN(B2:K2),0),{3,2,1}),COLUMN(B2:K2),B2:K2) )

Adjust range references and copy that formula down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"joewis" wrote:

I am secratary of golf league and handicap is calculated of the average of
the last 3 rounds played. If a player is absent for a week, there is a zero
in his score. This would be a rolling average of coarse.