SOLVED

Using ROUND with AVERAGE and LARGE within a formula.

%3CLINGO-SUB%20id%3D%22lingo-sub-646136%22%20slang%3D%22en-US%22%3EUsing%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646136%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20the%20formula%20to%20which%20I%20would%20like%20to%20add%20ROUND%20so%20that%20the%20average%20is%20rounded%20up%2Fdown.%26nbsp%3B%20This%20formula%20averages%20the%20last%205%20scores%20of%20the%20player%2C%20skipping%20the%20weeks%20that%20the%20player%20has%20not%20played.%26nbsp%3B%20In%20the%20next%20column%2C%20I%20have%20another%20formula%20which%20looks%20at%20my%20average%26nbsp%3B%20then%20matches%20it%20to%20a%20list%20of%20averages%20and%20corresponding%20handicaps.%26nbsp%3B%20At%20present%2C%20if%20I%20get%20an%20average%20of%2048.8%2C%20it%20will%20give%20me%20the%20handicap%20for%2048%20rather%20than%20rounding%20to%2049.%3C%2FP%3E%3CP%3EThis%20formula%20to%20average%20the%20last%205%20games%2C%20skipping%20blanks%20is%3A%3C%2FP%3E%3CP%3E%3DIF(COUNT(%24K%245%3AK38)%26gt%3B%3D5%2CAVERAGE(INDEX(%24K%245%3AK38%2CLARGE(IF(%24K%245%3AK25%26lt%3B%26gt%3B%22%22%2CROW(%24K%245%3AK25)-ROW(%24K%245)%2B1)%2C5))%3AK25)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20help%20with%20how%20to%20insert%20ROUND%20as%20I%20get%20an%20error%20every%20time%20I%20try.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-646136%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646189%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348815%22%20target%3D%22_blank%22%3E%40Naturelover07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(COUNT(Scores)%26gt%3B%3D5%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3EROUND(%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CSPAN%3EAVERAGE(%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CSPAN%3EINDEX(Scores%2C%20LARGE(IF(%24K%245%3AK25%26lt%3B%26gt%3B%22%22%2CROW(%24K%245%3AK25)-ROW(%24K%245)%2B1)%2C5))%20%3A%20K25%20)%2C%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E0%20)%2C%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20not%20sure%20I%20under%20the%20significance%20of%20K25%20in%20a%20range%20that%20goes%20to%20K38%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648661%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Peter.%20Using%20your%20changes%20to%20the%20formula%20doesn't%20give%20me%20an%20average%20but%20a%20blank.%26nbsp%3B%20%26nbsp%3BThe%20reference%20to%20K25%20was%20due%20to%20my%20cursor%20being%20on%20that%20line%20when%20I%20copied%20the%20formula.%26nbsp%3BI%20will%20try%20to%20upload%20my%20sheet%20for%20you%20to%20have%20a%20full%20view.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648705%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348815%22%20target%3D%22_blank%22%3E%40Naturelover07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20you%20formulas%20and%20so%20far%20I%20found%20no%20need%20for%20rounding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648860%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348815%22%20target%3D%22_blank%22%3E%40Naturelover07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hadn't%20realised%20it%20was%20a%20running%20value%20rather%20than%20a%20terminal%20value%20you%20are%20looking%20for.%3C%2FP%3E%3CP%3EI%20have%20implemented%20the%20AVERAGE%20in%20your%20workbook%20without%20adding%20any%20defined%20names.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648879%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BThanks!%26nbsp%3BIt%20works%20well.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20explain%20how%20you%20got%20the%20cells%20in%20that%20particular%20column%20to%20average%20without%20having%20the%20AVERAGE%20formula%20in%20each%20cell%3F%26nbsp%3B%20I%20will%20have%20to%20implement%20it%20in%2045%20other%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648896%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ROUND%20with%20AVERAGE%20and%20LARGE%20within%20a%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDisregard%20my%20last%20message%2C%20I%20realized%20that%20I%20was%20seeing%20only%20part%20of%20the%20formula.%26nbsp%3B%20I%20am%20very%20grateful%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

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

Highlighted

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

Highlighted

@Naturelover07 

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

 

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.

Highlighted

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

 

Highlighted

@Peter Bartholomew 

 

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