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

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gilbert De Ceulaer" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
>

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)

"Gilbert De Ceulaer" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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
>
>
>

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.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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)
>
> "Gilbert De Ceulaer" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

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)

"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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)
> >
> > "Gilbert De Ceulaer" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> >> 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
> >>
> >>
> >>
> >
> >
>
>

Did exactly what I wanted. Thank you, Bernard

"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Gilbert De Ceulaer" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

Impressive!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:u2gk%(E-Mail Removed)...
> 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)
>
> "Bernard Liengme" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > 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)
>> >
>> > "Gilbert De Ceulaer" <(E-Mail Removed)> wrote in message
>> > news:#(E-Mail Removed)...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>