Average of last X non-blank, non-contiguous cells

[Ann Scharpf]

I have two sheets, one is a summary sheet with a chart,
another has detailed data. In the detailed data sheet,
for each billing period, I have 6 columns. In the last
column, the total billing appears.

In my summary sheet, I have two series that I'm charting:
Actual Bills and Estimated Bills. For the actual, I have
a sum of all the total bills for each period up to the
date related to the current cell. (Oct1+Oct16+Nov1...)

I am having problems with the Estimate. My company wants
to have the average of the last three actual billing
periods. How do I tell Excel to look at a non-contiguous
series of cells and average the LAST three that are non-
blank? (If I'm generating the chart for the April 1 bill,
the estimated bills from April 16 through the rest of the
year should have the same average.)

Is this possible to do?

Thanks for any help you can give me.

Ann Scharpf

 

[Jason Morin]

As an example, assume your range to be A1:A20. Try:

=AVERAGE(OFFSET(A1,LARGE(IF(LEN(A1:A20)>0,ROW(A1:A20)),3)-
1,,ROWS(A1:A20)-LARGE(IF(LEN(A1:A20)>0,ROW(A1:A20)),3)))

Array-entered. Array formulas require that you press
ctrl/shift/enter.

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

One way

=AVERAGE(A10000:INDEX(A1:A10000,LARGE(ROW(A1:A10000)*(A1:A10000<>""),3)))

entered with ctrl + shift & enter