Need To Modify An Existing Formula To Ignore Blank/Zero Cells When Calculating An Average

[bobr31]

Hi,

I am new to the forum and am looking for help modifying a formula. I am running a small golf league and need help modifying an array that I am using to calculate average scores.

The criteria is to take the last 6 scores, drop the 2 highest and then take the average of the 4 lowest then multiply the result by .95. Here's the formula I am using.

{=AVERAGE(SMALL(IF(C9:H9<>0,C9:H9),ROW(INDIRECT("1:"&MIN(4,COUNTIF(C9:H9,">0"))))))*0.95} The data is in a range from C22:P36

The spreadsheet calculates the correct average score when a golfer has not missed a week but fails when there are any zeros in the rows for missed weeks. Below, Bob's score calculates fine, but Jim's doesn't. I have been moving scores every week when someone misses, but that is a hassle.

Scores
Bob 45 48 55 45 43 42 49 50 55 52 43 46
Jim 55 49 0 53 0 55 51 50 0 43 0 50

The problem is that I am using a defined range that only looks back at the last 6 cells. I need the formula to keep looking back until it finds the last 6 cells excluding ones that are 0/blank and then get the average etc. I can't figure out how to get the formula to look back until it finds the lowest 4 of 6 scores that are <>0.

I have searched the forum but haven't found a thread with the answer. I was going to attach a sample spreadsheet, but learned that that is not possible.

Any help would be appreciated.

Many Thanks,

Bob

 

[Aladin Akyurek]

Thanks. I was again forgetting the multiplier. Why don't we have an average in wk8 for Jim?

 

[XOR LX]

Players with less than 6 scores would use the average of available scores until they reach 6 scores.

Why are the cells in such cases blank in your latest table then?

My formula would be (in B9 and copy across and down):

=0.95*AVERAGE(AGGREGATE(16,6,AGGREGATE(15,6,INDEX(2:2,N(IF(1,AGGREGATE(14,6,COLUMN($B2:B2)/ISNUMBER(1/$B2:B2),{1,2,3,4,5,6})))),{1,2,3,4}),{0,1,2,3}/3))

though I'm not sure how you want to treat any errors, nor, as I said, why you have those blanks I referred to in your table, which will not be such using the above.

Note that this solution assumes that the order of names in A2:A5 will be identical to that for those in A9:A12.

Regards

 

[Aladin Akyurek]

Thanks. I was again forgetting the multiplier. Why don't we have an average in wk8 for Jim?

Your output exhibit of post #7 seems to suggest that the formula must be adapted to start in Wk6 (G9). If so:

G9, control+shift+enter, not just enter, copy across, and down:

=IF(INDEX(FREQUENCY($B2:G2,0),2)>=6,
   AVERAGE(SMALL(IF(COLUMN($B2:G2)>=LARGE(IF(ISNUMBER(1/$B2:G2),
   COLUMN($B2:G2)),MIN(6,INDEX(FREQUENCY($B2:G2,0),2))),
   IF($B2:G2>0,$B2:G2)),{1,2,3,4}))*0.95,"")

Check out whether you would want 4 instead of the first occurrence of 6.

 

[bobr31]

I tried the one suggested by XOR LX (Thanks for the suggestion) but I had trouble adapting it to work as well in my master.

Aladin, I copied your formula into my master league spreadsheet and it appears to work GREAT! Thanks.

I tweaked it for 4 occurrences and that worked as well. Good suggestion. I don't fully understand it, but it works perfectly. What I want to do for new players is take their average scores up to 4 scores and then 4 of 5 and then 4 of 6 when they have 6+ scores. Will the formula do that with occurrence=4? Also, if I wanted to move to best 4 of 7 scores, how would I tweak the formula?

Thanks,

Bob

 

[Aladin Akyurek]

I tried the one suggested by XOR LX (Thanks for the suggestion) but I had trouble adapting it to work as well in my master.

Aladin, I copied your formula into my master league spreadsheet and it appears to work GREAT! Thanks.

I tweaked it for 4 occurrences and that worked as well. Good suggestion. I don't fully understand it, but it works perfectly. What I want to do for new players is take their average scores up to 4 scores and then 4 of 5 and then 4 of 6 when they have 6+ scores. Will the formula do that with occurrence=4? Also, if I wanted to move to best 4 of 7 scores, how would I tweak the formula?

Thanks,

Bob

Let's define two parameters:

[1] LastN (last >0 scores)
[2] SmallestP (smallest subset < or equal to LastN)

Instead of {1,2,3,4}, we can encode variable SmallestP in the formula with: ROW(INDIRECT("1:"&SmallestP)).

That is:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]Scores[/td][td]Wk1[/td][td]Wk2[/td][td]Wk3[/td][td]Wk4[/td][td]Wk5[/td][td]Wk6[/td][td]Wk7[/td][td]Wk8[/td][td]Wk9[/td][td]Wk10[/td][td]Wk11[/td][/tr]

[tr][td]

2​

[/td][td]Bob[/td][td]

45​

[/td][td]

48​

[/td][td]

55​

[/td][td]

45​

[/td][td]

43​

[/td][td]

42​

[/td][td]

49​

[/td][td]

50​

[/td][td]

55​

[/td][td]

52​

[/td][td]

43​

[/td][/tr]

[tr][td]

3​

[/td][td]Jim[/td][td]

55​

[/td][td]

49​

[/td][td]

0​

[/td][td]

53​

[/td][td]

0​

[/td][td]

55​

[/td][td]

51​

[/td][td]

50​

[/td][td]

0​

[/td][td]

43​

[/td][td]

0​

[/td][/tr]

[tr][td]

4​

[/td][td]Dan[/td][td][/td][td][/td][td][/td][td]

53​

[/td][td]

0​

[/td][td]

55​

[/td][td]

51​

[/td][td]

50​

[/td][td]

0​

[/td][td]

43​

[/td][td]

0​

[/td][/tr]

[tr][td]

5​

[/td][td]Jon[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

48​

[/td][td]

51​

[/td][td]

50​

[/td][td]

0​

[/td][td]

43​

[/td][td]

0​

[/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Averages[/td][td]Wk1[/td][td]Wk2[/td][td]Wk3[/td][td]Wk4[/td][td]Wk5[/td][td]Wk6[/td][td]Wk7[/td][td]Wk8[/td][td]Wk9[/td][td]Wk10[/td][td]Wk11[/td][/tr]

[tr][td]

9​

[/td][td]Bob[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

41.563​

[/td][td]

42.275​

[/td][td]

42.513​

[/td][td]

42.513​

[/td][td]

43.7​

[/td][td]

43.7​

[/td][/tr]

[tr][td]

10​

[/td][td]Jim[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

50.35​

[/td][td]

49.4​

[/td][td]

48.213​

[/td][td]

48.213​

[/td][td]

45.838​

[/td][td]

45.838​

[/td][/tr]

[tr][td]

11​

[/td][td]Dan[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

49.638​

[/td][td]

49.638​

[/td][td]

46.788​

[/td][td]

46.788​

[/td][/tr]

[tr][td]

12​

[/td][td]Jon[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

45.6​

[/td][td]

45.6​

[/td][/tr]
[/table]

G9, control+shift+enter, copy across, and down:

=IF(INDEX(FREQUENCY($B2:G2,0),2)>=SmallestP,
   AVERAGE(SMALL(IF(COLUMN($B2:G2)>=LARGE(IF(ISNUMBER(1/$B2:G2),COLUMN($B2:G2)),
   MIN(LastN,INDEX(FREQUENCY($B2:G2,0),2))),IF($B2:G2>0,$B2:G2)),
   ROW(INDIRECT("1:"&SmallestP))))*0.95,"")

See the workbook that implements the set up: https://dl.dropboxusercontent.com/u...ater than 0 scores parameterized-version.xlsx

 

[bobr31]

Excellent. Thank You Aladin! Your solution works great and allowed me to run several scenarios to modify handicaps for this season and to rebuilt my entire league spreadsheet. I also learned a lot with your help. I used to be an excel geek, but haven't done much with it for about 10 years and this got me going again. Again, thanks for the help!

 

[Aladin Akyurek]

Excellent. Thank You Aladin! Your solution works great and allowed me to run several scenarios to modify handicaps for this season and to rebuilt my entire league spreadsheet. I also learned a lot with your help. I used to be an excel geek, but haven't done much with it for about 10 years and this got me going again. Again, thanks for the help!

You are welcome. Great to restart. And thanks for the nice feedback.