Dynamic Average Orders Per Day Counter By Date

Question

I have been having some trouble figuring this out for the last hour, I hope one of you can help me out :)

So I have figured out the formula to calculate the average orders per day like so:

{=COUNTA(A2:A7)/SUM(1/COUNTIF(A2:A7,A2:A7))}  'PRESS (CTRL + SHIFT + ENTER)'

DATA:

         A
1 |  ORDER DATE
2 |  12/05/2019
3 |  12/05/2019
4 |  12/05/2019
5 |  15/05/2019
6 |  16/05/2019
7 |  22/05/2019
8 |
9 |
10|

RESULT:

1.5

My formula works fine to calculate only until A7. How would I make the formula dynamic so that it can calculate future data entries until A10 and/or infinity down the A column?

Thank you

Answer 1

The cell with the last date in column A can be retrieved with,

index(A:A, match(1e99, A:A))

Put this into your formula like,

=COUNTA(A2:index(A:A, match(1e99, A:A)))/SUM(1/COUNTIF(A2:index(A:A, match(1e99, A:A)), A2:index(A:A, match(1e99, A:A))))

This is a non-volatile solution (unlike OFFSET). It will only recalculate when something that affects the formula changes; not when anything in the workbook changes.

Answer 2

Try,

A2:A7 -> OFFSET(A2,,,COUNTA(A:A)-1)

= COUNTA(OFFSET(A2,,,COUNTA(A:A)-1))/SUM(1/COUNTIF(OFFSET(A2,,,COUNTA(A:A)-1),OFFSET(A2,,,COUNTA(A:A)-1)))