• 667K Members
• 9,373 Online
• 822K Conversations
SOLVED

Highlighted
New Contributor

# 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.

6 Replies
Highlighted

# Re: Using ROUND with AVERAGE and LARGE within a formula.

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

Highlighted

# Re: Using ROUND with AVERAGE and LARGE within a formula.

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.

Highlighted

# Re: Using ROUND with AVERAGE and LARGE within a formula.

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

Highlighted
Solution

# Re: Using ROUND with AVERAGE and LARGE within a formula.

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.

Highlighted

# Re: Using ROUND with AVERAGE and LARGE within a formula.

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.

Highlighted

# Re: Using ROUND with AVERAGE and LARGE within a formula.

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