OFFSET to include blank cells

[GMCN]

Hi, help required please before this computer exits through the nearest
window!

I am using the formula

=AVERAGE(OFFSET(D6,COUNTIF(D727,">=0%"),,-5,))

to give me a % average over the last 5 entries in my worksheet. After
much searching of various groups I managed to get it to average those
cells which are not blank. However I still cannot fathom how to get
OFFSET to accept the fact that there are blank cells. EG if D7 happens
to be blank then the last five entries start from the 2nd last entry.
Also if there happens to be a blank within the column then OFFSET
starts its count again from the next available cell containing data.

I need to have a formula which averages the last 5 entries which are >=
0 no matter if there are empty cells in between. 1 cell in the column
will be filled from the results of another sheet's activity each
working day throughout the month so I do not want the formula to
activate until at least 5 entries have been made.

I seem to be asking for quite a lot here but I would be grateful if
anybody could help with any part of my request.

Thank you in advance.

 

[Bob Phillips]

How about

=AVERAGE(INDEX(D727,LARGE(IF(D727<>"",ROW(7:27)-MIN(ROW(7:27))+1),5))2
7)

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[GMCN]

Cheers Bob that worked a treat. I've given this post a top rating.

Glen