Averaging last 25 non zero values.
wrote in message
...
Hi,
Have a dynamic list of sales values and want to make several
calculations on them, most of which I can do myself.... However, I
want to find an average of the last 25 (non-zero) values without using
a macro is this possible??
Many thanks in advance,
Peter
Here's an adaptation of one I developed some time ago. It's an array
formula, so you need to use CTRL+SHIFT+ENTER (rather than just ENTER) when
you first enter it and whenever you edit it.
=AVERAGE(OFFSET(A1,LARGE(ISNUMBER(A1:A100)*(ROW(A1 :A100)-ROW(A1)),25),0,ROWS(A1:A100)-LARGE(ISNUMBER(A1:A100)*(ROW(A1:A100)-ROW(A1)),25)))
|