SOLVED
Home

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
Naturelover07
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

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

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

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

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

 

@Peter Bartholomew 

 

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

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies