Average of a series

  • Thread starter Thread starter vano
  • Start date Start date
V

vano

HI,
Yes the first formula works. Thanks you very much.
The 2nd formula did not.
I am astounded with what can be done in excel if you know how to us
it.

Just one last Q
If I want to change the average to say the last 20 cells with number
in it what do I need to change in the code below.
I changed 5 to 20 but I got an error. The code need to reference a
least 50 cells to find 20 numbers.

=AVERAGE(IF(INDEX(A:A,LARGE(IF(A1:A23<>0,ROW(A1:A23)),5)):INDEX(A:A,LARGE(IF(A1:A23<>0,ROW(A1:A23)),1))<>0,INDEX(A:A,LARGE(IF(A1:A23<>0,ROW(A1:A23)),5)):INDEX(A:A,LARGE(IF(A1:A23<>0,ROW(A1:A23)),1))))

...entered using CONTROL+SHIFT+ENTER.

Thanks in advance,

Va
 
Change the formula to...

=AVERAGE(IF(INDEX(A:A,LARGE(IF(A1:A50<>0,ROW(A1:A50)),20)):INDEX(A:A,LARGE(IF(A1:A50<>0,ROW(A1:A50)),1))<>0,INDEX(A:A,LARGE(IF(A1:A50<>0,ROW(A1:A50)),20)):INDEX(A:A,LARGE(IF(A1:A50<>0,ROW(A1:A50)),1))))

...entered using CONTROL+SHIFT+ENTER.

Note that you'll need at least 20 numbers in your list, otherwis
you'll get an #NUM! error.

Change the second formula to...

=AVERAGE(SUBTOTAL(9,OFFSET(A1,LARGE(IF(A1:A50<>0,ROW(A1:A50)),ROW(INDIRECT("1:20")))-1,0)))

...entered using CONTROL+SHIFT+ENTER.

You say you're still having problems with this one. Where's your lis
of numbers and exactly what formula are you using? If you want, I ca
e-mail you a sample
 
Back
Top