Forum Discussion
Using ROUND with AVERAGE and LARGE within a formula.
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.
I 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.
6 Replies
=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?
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.
I 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.
