[SOLVED] Averageif bottom n if not blank

Averageif bottom n if not blank

I'd like to find the average of the bottom n (3 in this case) of a range. These will be golf scores and updated weekly. It will be 3 weeks before I have 3 numbers in the range, but I will need an average before that too. Eventually there will be 9 numbers and I'll want the average of the smallest 3. I guess it's kind of like a running average.

A	B	C	D	E	F	G	H	I
40	42

For this example, I'd like the average to be 41.

A	B	C	D	E	F	G	H	I
40	39	43	45	48

For this example, I'd like the average to be 40.7, using the 3 smallest (40,39,43).

I have triedAVERAGE(SMALL(A1:I1,{1,2,3})), but it only works if I have at least 3 values.I've also tried AVERAGEIF(A1:I1,""), but that just gives me the overall average, not the low 3. I tried inserting the AVERAGE(SMALL(A1:I1,{1,2,3})) in the AVERAGEIF function, but I get an error. Let me know if you need more info.

Solution

Give a try to the following formula-

=AVERAGE(TAKE(A1:I1,,3))