Moving Average with Blanks
 

My employees play on a golf league. I've helped them set up a spreadsheet to
track scores. But they've come up with a situation I'm stuck on.
The names are in column B with columns C through Q used to record the weekly
golf score. They'd like to record a moving average in column W, showing the
last four scores for each player. Some players cannot make it every week, so
the cell remains blank.
Also, some substitutes may not have four outings in yet. So the ideal
formula would have the simple average if fewer than four rounds are recorded
(so I guess it would start with =if(count(c4:Q4)<4,AVERAGE(C4:Q4), ...) or
the last four rounds actually played by the golfer for the regulars (to fill
in the elipse of my formula).
I'd prefer to keep it as a formula if possible. I think I could figure out
some code for this but I'm wondering if we can get it with a formula.
Peace.
EQC

Moving Average with Blanks
 

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

"E.Q." wrote:

My employees play on a golf league. I've helped them set up a spreadsheet to
track scores. But they've come up with a situation I'm stuck on.
The names are in column B with columns C through Q used to record the weekly
golf score. They'd like to record a moving average in column W, showing the
last four scores for each player. Some players cannot make it every week, so
the cell remains blank.
Also, some substitutes may not have four outings in yet. So the ideal
formula would have the simple average if fewer than four rounds are recorded
(so I guess it would start with =if(count(c4:Q4)<4,AVERAGE(C4:Q4), ...) or
the last four rounds actually played by the golfer for the regulars (to fill
in the elipse of my formula).
I'd prefer to keep it as a formula if possible. I think I could figure out
some code for this but I'm wondering if we can get it with a formula.
Peace.
EQC

Moving Average with Blanks
 

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

Moving Average with Blanks
 

then you could do a pivot table.

name / date / week # / round # / # for last 4 / score

where # for last 4 could either be Y or N or a number from 1-4 , then when
you do the pivot table, filter on that to pull in only the last 4. This may
require some effort to sort your data and make sure to count the last 4 of
each person. How many people are you keeping track of? If not a bunch, then
maybe a sheet for each and then one sheet to link , then the pivot off of
that, then you could put a formula easer for the last 4?

"E.Q." wrote:

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