Moving Average Question? Help

  • Thread starter Thread starter gillemi
  • Start date Start date
G

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
 
=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)
 
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!!
 
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)
 
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!!
 
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)
 
Back
Top