AVERAGE of the previous meaningful cells

[Gilbert De Ceulaer]

B10 = AVERAGE(A1:A10),
B11 = AVERAGE(A2:A11), etc.

In case there are empty cells in the A-column, the result is not correct
anymore because AVERAGE does not take en empty cell into account.
For instance if A1:A10 are 1, A11:A20 are empty and A21:A30 are 2, the above
formula in B21 would give AVERAGE(A12:A21), or 2.

What I want is the average of the previous 10 meaningful cells so - in this
case - the average of A2:A10 and A21, or (9*1+2)/10 = 1.1

Question : Is there a possibility to calculate the average of the previous
10 meaningful cells (0 is meaningful, blank is not)

Thanks in advance.
Gilbert

 

[Bernard Liengme]

Hi Gilbert,
I found it difficult to think 'backwards' with Excel function so I did it in
VBA
With =myavg(A21) this gives 1.1 for your data

Function myAvg(mystart)
Application.Volatile

Set mycell = mystart
mysum = 0#
mycount = 0#
Do While mycount < 10
If Application.WorksheetFunction.IsNumber(mycell.Value) Then
mysum = mysum + mycell.Value
mycount = mycount + 1
End If
Set mycell = mycell.Offset(-1, 0)
Loop
myAvg = mysum / 10#

End Function

best wishes

 

[Bob Phillips]

Try this in B10

=AVERAGE(LARGE(IF(A$1:A10<>"",A$1:A10),ROW(INDIRECT("1:10"))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter. Copy down.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bernard Liengme]

Not sure this works.
If data in A is 99, 99,99 99, 1,1,1,1,1,1, <10 blanks>,
2,2,2,2,2,2,2,2,2,2,2,
Value from formula after B20 are not correct because of the LARGE function.

 

[Bob Phillips]

You're right, shouldn't have relied on the OP's data.

Maybe this is better

=SUM(SUBTOTAL(9,OFFSET($A$1,LARGE(IF($A$1:$A10<>"",ROW($A$1:$A10)),ROW(INDIR
ECT("1:10")))-1,0)))/MIN(COUNT($A$1:$A10),10)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Gilbert De Ceulaer]

Did exactly what I wanted. Thank you, Bernard

Hi Gilbert,
I found it difficult to think 'backwards' with Excel function so I did it
in VBA
With =myavg(A21) this gives 1.1 for your data

Function myAvg(mystart)
Application.Volatile

Set mycell = mystart
mysum = 0#
mycount = 0#
Do While mycount < 10
If Application.WorksheetFunction.IsNumber(mycell.Value) Then
mysum = mysum + mycell.Value
mycount = mycount + 1
End If
Set mycell = mycell.Offset(-1, 0)
Loop
myAvg = mysum / 10#

End Function

best wishes

 

[Bernard Liengme]

Impressive!