Average of last 3 non-0 numbers in row?

[Guest]

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.

 

[Bob Phillips]

=AVERAGE(N(OFFSET($B$2,0,LARGE(IF($B$2:I2<>0,COLUMN($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)

 

[Guest]

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