Using ROUND with AVERAGE and LARGE within a formula.

[Naturelover07]

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.

[#2]

@Naturelover07 

=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?

[#3]

@Peter Bartholomew 

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. 

[#4]

@Naturelover07 

I changed you formulas and so far I found no need for rounding.

[#5 · Accepted solution]

@Naturelover07 

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]

@Peter Bartholomew 

 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.

[#7]

@Peter Bartholomew 

Disregard my last message, I realized that I was seeing only part of the formula.  I am very grateful for your help.