Averaging last 25 non zero values.
Maybe
=AVERAGE(LARGE( SUBTOTAL(9,
OFFSET(A1:A100,LARGE(IF(A1:A100<0,ROW(A1:A100)-MIN(ROW(A1:A100))),ROW(INDIRECT("1:25"))),0,1)),{1 ,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25}))
It's an array so enter with Ctrl+Shift+Enter
Works on the range A1 - A100 so change to suit.
There must be a better way so lets wait for other solutions
Mike
" wrote:
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
|