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]

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

avg

2​

Bob

45​

48​

55​

45​

43​

42​

49​

50​

55​

52​

43​

46​

47​

3​

Jim

55​

49​

0​

53​

0​

55​

51​

50​

0​

43​

0​

50​

48.5​

4​

Dan

53​

0​

55​

51​

50​

0​

43​

0​

0​

49.25​

5​

Jon

51​

50​

0​

43​

0​

0​

48​

O2, control+shift+enter, not just enter, and copy down:

=AVERAGE(SMALL(IF(COLUMN(B2:M2)>=LARGE(IF(ISNUMBER(1/B2:M2),
    COLUMN(B2:M2)),MIN(6,INDEX(FREQUENCY(B2:M2,0),2))),IF(B2:M2>0,B2:M2)),
    ROW(INDIRECT("1:"&MIN(4,COUNTIF(B2:M2,">0"))))))

 

[Mike LH]

Hi,

You lost me a bit over the ranges but this does C22:P22 and you can drag down as required. It's an ARRAY formula so CTRL+Shift+Enter


=AVERAGE(SMALL(IF(P22:INDEX(A22:P22,LARGE(COLUMN(C22:P22)*(C22:P22>0),6))>0,P22:INDEX(A22:P22,LARGE(COLUMN(C22:P22)*(C22:P22>0),6))), {1,2,3,4}))*0.95

EDIT... I should have added that if you leave your none-score cells blank instead of zero then the formula is much simpler.


=AVERAGE(SMALL(P22:INDEX(A22:P22,LARGE(COLUMN(C22:P22)*(C22:P22>0),6)), {1,2,3,4}))*0.95

 

[Aladin Akyurek]

@bobr31

Addendum: Add the the multiplier bit, i.e., *0.95, to the formula suggested in post #2.

 

[XOR LX]

You can also use the shorter - and non-array:

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

again based on values in B2:M2.

Copy down as required.

Regards

 

[bobr31]

Hi Guys,

Wow, thanks for the quick replies! Let me plug them into my spreadsheet and see how they look and I'll let you know.

Again, THANKS!!

Bob

 

[bobr31]

Hi Guys,

I was able to get the formulas to work at the end of the rows...all of them. Thanks! However, my spreadsheet is set up to calculate the weekly averages as indicated below. I haven't been able to convert any of the formulas provided to work across vs down. The values in the Averages section are accurate calculations.

Any suggestions on how I need to modify any of the formulas to work in a row vs column?

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

Row/Col	A	B	C	D	E	F	G	H	I	J	K	L
1	Scores	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11
2	Bob	45	48	55	45	43	42	49	50	55	52	43
3	Jim	55	49	0	53	0	55	51	50	0	43	0
4	Dan				53	0	55	51	50	0	43	0
5	Jon						48	51	50	0	43	0
6
7
8	Averages	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11
9	Bob						41.56	42.28	42.51	42.51	43.7	43.7
10	Jim									48.2	45.8	45.8
11	Dan										47.26	47.26
12	Jon											45.6

<tbody>
</tbody>

Thanks,

Bob

 

[XOR LX]

Are you recording your averages directly below the table data, as here? Does this not make a set-up in which additional, future data can be added for each player problematic?

Regards

 

[Aladin Akyurek]

How does 41.56 of G9 for Bob obtain, that is, of which numbers is this average made of?

 

[bobr31]

Are you recording your averages directly below the table data, as here? Does this not make a set-up in which additional, future data can be added for each player problematic?

Regards

The averages are computed below the actual score data. I left off blank cells beyond column L in my example. New scores for Bob would be posted in cells M2 and beyond. His best 4 of last 6 average non-zero scores would be calculated in cells M9 and beyond. New scores would be added every week in cells to the right of Col L. New players would be added below Row 5, although adding players is a once a year task.

Row/Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Scores	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	WK12	WK13	WK14	WK15	WK16
2	Bob	45	48	55	45	43	42	49	50	55	52	43
3	Jim	55	49	0	53	0	55	51	50	0	43	0
4	Dan				53	0	55	51	50	0	43	0
5	Jon						48	51	50	0	43	0
6
7
8	Averages	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12	Wk13	Wk14	Wk15	Wk16
9	Bob						41.56	42.28	42.51	42.51	43.7	43.7
10	Jim									48.2	45.8	45.8
11	Dan										47.26	47.26
12	Jon											45.6

<tbody>
</tbody>

How does 41.56 of G9 for Bob obtain, that is, of which numbers is this average made of?

The criteria for getting the average score 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. 41.56 in G9 is calculated by taking the 4 smallest of the most recent 6 non-zero scores posted in B9-G9 and multiplying by .95.