Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for average of cells that have blanks | Excel Worksheet Functions | |||
Average not including Zeros/Blanks | Excel Worksheet Functions | |||
Criteria average ignoring blanks | Excel Discussion (Misc queries) | |||
Skipping a cell and moving on to the next, with no blanks in betwe | Excel Discussion (Misc queries) | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) |