AVERAGE last 14 entries in a column

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Manually add some results and if it's not blindingly obvious how you have arrived at them some explanatory notes as well.

if it is a simple 14-day rolling average, with your data un column A, wouldnt thisformula, copied down, work for you?

=IF(A18="","",AVERAGE(A5:A18))

the =IF(A18="","", will hide the answer if there is no value in A18 yet

Originally Posted by xLJer

This formula seems to work and gives you the average of the last 14 values in a column.
Assuming your data is in column A:

Please Login or Register  to view this content.

Cool, thanks for that. It appears to work but I am curious as to why it does not agree exactly with an AVERAGE formula using the actual range. If I use...

=AVERAGE(C47:C60)

...the results is .113. Using your formula, result is .114. (My data is in Col C, not Col A.)

P.S. How can I make a formula where I want it start at a call and then use everything below that? If I want to do, say, an AVERAGE, I could say AVERAGE(C:C) and that would all in the column, right? What if I want to start at, say, C5 and then everything below that? I know this has got to be simple.

attached

xLJer's formula works on the basis that column C is continually populated until the data ends.....but because you have blanks in C1:C3 it isn't averaging the correct range, it misses the bottom 3 values (so averages C44:C57 in your example).

This formula will average the last 14 values assuming you have at least 14 in there (even if there are gaps before the data - but assumes continuous data after that)

=AVERAGE(OFFSET(INDEX(C:C,MATCH(9.9E+307,C:C)),0,0,-14))

If you might have less than 14 values then you can add an IF function to cope with that, i.e.

=IF(COUNT(C:C)<14,AVERAGE(C:C),AVERAGE(OFFSET(INDEX(C:C,MATCH(9.9E+307,C:C)),0,0,-14)))

Note that I used AVERAGE(C:C) in there, I think that answers your last query. You can average the whole column even when you only have numbers from C5 because the text values and blanks in C1:C4 will be ignored by AVERAGE function

9.9E+307 is just about the biggest number you can enter in a cell in Excel, to find the last number in a column you can use a lookup with that value as the lookup value, e.g. for last number in ciolumn C

=LOOKUP(9.9E+307,C:C)

or to find the position of that value you can use MATCH the same way - that's what I did in the suggested formula.

To average everything after C6 you could simply use a row much larger than you'll ever need, e.g.

=AVERAGE(C6:C10000)

or to explicitly get the last value

=AVERAGE(C6:INDEX(C:C,MATCH(9.9E+307,C:C)))