The problem with a simple average is that they want the last four rounds
played to determine the handicap.
I did come up with one work around, but it has a design feature I'd prefer
to avoid.
Below the area where the scores are posted next to their names I created
cells the following formula (generalize off the first for column C to the
rest of the table above) in cell C34.
=IF(AND(NOT(ISTEXT(C4)),C49),COLUMN(C4),"")
This formula copied C34:u50. That should cover their season.
Column Z on these rows contains the formula
=IF(COUNT(C34:u34)4,LARGE(C34:u34,4),NA())
This tells me the column number containing the fourth most recent entry.
I use that to create the averages in cells X4:X20. The formula in X4 looks
like this:
=IF(COUNT(C8:U8)<4,AVERAGE(C8:U8),AVERAGE(OFFSET(B 8,0,Z38-2,1,Weeks+3-Z38)))
where "Weeks" is a named range consisting of the cell displaying the number
of weeks played to date.
This works, but I have a lot of extra stuff going on in rows below the
"active" data area. An ideal solution would be a single formula to determine
the row average for each row in the range X4:X20.
I won't be actively keeping this file up; the employee running this works
night shift so he might be on his own when things go awry.
Any thoughts of how to simplify?
Peace.
EQC
"Don" wrote:

if you average a range (sounds like from c through q) and one cell is blank,
the average function will ignore the blank. Since they do not sound like
they will have one for evey cell or they are having a blast for the week,
then leave it blank.

I would try using a pivot table also which should be much better, then you
can put on there if they paied for the beer? lol
name / date / week # / round # / score