Moving Average Question? Help

[gillemi]

I'm creating a spreadsheet to track averages for a golf league. Th
scores for an individual will be enetered across a row such a
(A1:A50). Each column will represent a week. I need a formula tha
will calculate the average of the last 10 weeks played. Some week
will be blank if that person missed a week. So I need it to ignor
zero, however, still count back to the last 10 played. Note: A1:A1
will be the last 10 played last year...so the formula will start i
A11

 

[Bob Phillips]

=AVERAGE(A50:INDEX(A11:A50,SUMPRODUCT(LARGE(ROW(A11:A50)*(A11:A50<>""),10)))
)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[gillemi]

Thanks for the response...

I think I described it wrong. I'm actually moving across th
spreadsheet from A1 to lets say T1.

The moving average formula of the last 10 played will start 1 row belo
and 10 columns out (J2 to T2).

The formula should count the last 10 golf scores ignoring blank/zero.

Thanks again!!

 

[Bob Phillips]

Okay, in J2

=AVERAGE(J1:INDEX($A1:J1,MIN(COUNT($A1:J1),
SUMPRODUCT(LARGE(COLUMN($A1:J1)*($A1:J1<>""),10)))))

still an array formula, copy across.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[gillemi]

Thanks again! It doesn't appear to be ignoring zero values, skipping
that cell and going back 10 more. The cell entries are in row 1 and
the answers that I am trying to get to are in row 2...

Row 1 (A1:T1): 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 0,
0, 55, 56, 57, 58, 59

Row 2 Answers (J2:T2): 44.5, 45.5, 46.5, 47.5, 47.5, 47.5, 48.7, 49.9,
51.1, 52.3, 53.5

thanks!!

 

[Bob Phillips]

You originally said some weeks will be blank. Zero is not blank. But if it
is zero

=AVERAGE(J1:INDEX($A1:J1,MIN(COUNT($A1:J1),
SUMPRODUCT(LARGE(COLUMN($A1:J1)*($A1:J1<>0),10)))))

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[gillemi]

It works, thank you!

Can you explain the formula?