Search code examples
excelaveragemoving-averagenonblank

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

    enter image description here