Gangsome Scoring.xlsxLooking for a formula that will go in E3 that will calculate the last 10 entries average score. Will drag formula down for all 50 players. Any help would be appreciated. First time on boards and tried several formulas from searches but cant get one that calculates correct values.
Try this formula in cell E1 and copy down:
Formula:
{=AVERAGE(IF($T$1:$GI$1<=TODAY(),IF($T$1:$GI$1>TODAY()-10,IF(NOT(ISBLANK($T3:$GI3)),$T3:$GI3))))}
It's an array formula so make sure it has the curly braces { } at either end when it's entered into the cell.
It averages rolling 10-day average of scores, excluding blanks. Note that it includes today's score in the 10 days. If you want it to look for the 10 days up to yesterday, then you want this formula:
Formula:{=AVERAGE(IF($T$1:$GI$1<TODAY(),IF($T$1:$GI$1>=TODAY()-10,IF(NOT(ISBLANK($T3:$GI3)),$T3:$GI3))))}
Have a great day!
Try this formula in cell E1 and copy down:
Formula:{=AVERAGE(IF($T$1:$GI$1<=TODAY(),IF($T$1:$GI$1>TODAY()-10,IF(NOT(ISBLANK($T3:$GI3)),$T3:$GI3))))}
It's an array formula so make sure it has the curly braces { } at either end when it's entered into the cell.
It calculates rolling 10-day average of scores, excluding blanks. Note that it includes today's score in the 10 days. If you want it to look for the 10 days up to yesterday, then you want this formula:
Formula:{=AVERAGE(IF($T$1:$GI$1<TODAY(),IF($T$1:$GI$1>=TODAY()-10,IF(NOT(ISBLANK($T3:$GI3)),$T3:$GI3))))}
Mr CPWalker all my best to you sir!!! Thank you so much it works perfectly except on some of the players I am getting a div0 error. It appears it was from
anyone that had rounds that were taken off GHIN handicap. That is done if a player does not have at least 10 rounds in the game yet. The heading had GHIN1
instead of any actual date so I think it was throwing the formula off. I replaced the column headers to reflect dates and still getting the same error. Gangsome Scoring.xlsx
Hey Lasers Reef, the #DIV/0! errors are caused by the fact that some players haven not played any games in the past 10 days. For those players the formula becomes zero divided by zero, which is what causes the error. Jerod Turner in row 4 is an example of this. The most recent score for him is on 8/1/2013. The formula is 10-day rolling average starting from today's date and going back 10 days.
OK now I see what it is calculating. I was looking for the last 10 rounds played (could have been over a year period) but I will use that as well to look at the past month for each player by changing the 10 in your formula to 30..
Thanks for the quick resolution!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks