Last 3 rounds Average

[Jeff]

I need to automate a rolling 3-round average for the golf league. The
scenario is like this: We play 20 weeks. Some folks miss a week or two
so we need a current week average of our last three rounds. I can
certainly figure the last three "weeks" out, but if they're blank, I
need to go back to the last round. Using our first 7 weeks, here's
what I'm trying to accomplish:

Week 1 2 3 4 5 6 7 Overall - Last 3
Jeff 88 87 93 89 94 -- 92 90.50 91.67
Mike 90 98 93 94 -- 90 88 92.17 90.67
Tom 79 80 -- 82 -- 87 -- 82.00 83.00
Jim 85 87 79 -- 82 81 -- 82.80 80.67

I only need the formula for the "Last 3" column.

Any ideas?
Jeff...

 

[Bob Phillips]

=AVERAGE(H2:INDEX( A2:H2,SUMPRODUCT(LARGE(COLUMN(A1:H1)*(A2:H2<>""),3))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jeff]

This works !!! Thank you!!

I have a few questions though. Do I need to "freeze" the A1:H1 ? So
it would read A$2:H$2? I'm not sure I understand the formula. I've
read the HELP on the formulas but am still not 100% confident. What if
I add 5 more columns?

Thank in advance!
Jeff...

 

[Bob Phillips]

Jeff,

=AVERAGE(H2:INDEX( A2:H2,SUMPRODUCT(LARGE(COLUMN(A1:H1)*(A2:H2<>""),3))))

The first H2 refers to the last cell in the range of scores, so adjust that.

Column(A1:H1) is used to build an array of column numbers, and is used in
conjunction with (A2:H2<>"") to build an array of column numbers for
populated columns. LARGE,..,3 is used to find the 3rd largest, so we only
get the last 3 numbers. I used row 1 in COLUMN(A1:H1) as it does not relate
to the row of data, just the columns. so could just as easily have been
COLUMN(A2:H2). The SUMPRODUCT is just sums the first valid column to itself,
to provide a clean number to the INDEX function.

INDEX(A2:H2, that column value) is then used to get the first cell to count,
and is used in conjunction with the first H2, the last data cell, to get the
3 lasts data cells to AVERAGE.

So ...

The first H2 refers to the last cell in the range of scores, so adjust that.

A2:H2 refers to the data cells being examined, so adjust the H2 in each of
those (note I use A2, even though that may not be a score, but a name
perhaps, so as to keep absolute column numbers, not relative columns.

A1:H1 is used for the columns array, so adjust the H to the last column, and
I would adjust to row 2 as well.

In summary, if you want to extend 5 columns, it would now be

=AVERAGE(M2:INDEX( A2:M2,LARGE(COLUMN(A2:M2)*(A2:M2<>""),3)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Gary Keramidas]

i have something i can send you if you want. did it for my brother a few years
ago.

 

[Jeff]

Thank you very much!

 

[Jeff]

Gary, thanks forthe offer, but Bob's formula worked perfectly.