May 25 2019 11:30 AM
This is the formula to which I would like to add ROUND so that the average is rounded up/down. This formula averages the last 5 scores of the player, skipping the weeks that the player has not played. In the next column, I have another formula which looks at my average then matches it to a list of averages and corresponding handicaps. At present, if I get an average of 48.8, it will give me the handicap for 48 rather than rounding to 49.
This formula to average the last 5 games, skipping blanks is:
=IF(COUNT($K$5:K38)>=5,AVERAGE(INDEX($K$5:K38,LARGE(IF($K$5:K25<>"",ROW($K$5:K25)-ROW($K$5)+1),5)):K25),"")
Would appreciate help with how to insert ROUND as I get an error every time I try.
May 25 2019 02:45 PM - edited May 25 2019 02:47 PM
=IF(COUNT(Scores)>=5,
ROUND(
AVERAGE(
INDEX(Scores, LARGE(IF($K$5:K25<>"",ROW($K$5:K25)-ROW($K$5)+1),5)) : K25 ),
0 ),
"")
I am not sure I under the significance of K25 in a range that goes to K38?
May 26 2019 07:43 AM
Thanks Peter. Using your changes to the formula doesn't give me an average but a blank. The reference to K25 was due to my cursor being on that line when I copied the formula. I will try to upload my sheet for you to have a full view.
May 26 2019 08:54 AM
May 26 2019 12:18 PM
SolutionI hadn't realised it was a running value rather than a terminal value you are looking for.
I have implemented the AVERAGE in your workbook without adding any defined names.
May 26 2019 12:52 PM
Thanks! It works well.
Can you explain how you got the cells in that particular column to average without having the AVERAGE formula in each cell? I will have to implement it in 45 other worksheet.
May 26 2019 01:09 PM
Disregard my last message, I realized that I was seeing only part of the formula. I am very grateful for your help.
May 26 2019 12:18 PM
SolutionI hadn't realised it was a running value rather than a terminal value you are looking for.
I have implemented the AVERAGE in your workbook without adding any defined names.