Average of a series

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

vano

Actually this way still wont work how I need it.
If someone can please help I will be very gratful.
I will explain the problem again

below is the data sample
0.00
0.00
212.50
0.00
700.00
0.00
0.00
0.00
775.00
0.00
675.00
575.00
0.00
725.00
0.00
0.00
0.00
0.00
0.00
1012.50
0.00
0.00
0.00

I need to get the average of the last 5 cells that have a number o
it.
so if the cell =0, I need to omit it from the equation all together.
Eg on the last 5 cells you can see there is only 1012.50.
To use an IFCOUNT formula will not work
I need to get the average for the last 5 cells with a number in it.
being
1012.50
725
575
675
775

if the next cell is 0 I just want to keep the same average as the cel
before.

Thanks in advance,

Regards,

Va
 
=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.

Hope this helps
 
Replace my formula with the following, more efficient, array formula...

=AVERAGE(SUBTOTAL(9,OFFSET(A1,LARGE(IF(A1:A23<>0,ROW(A1:A23)),{1,2,3,4,5})-1,0)))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average of a series 2
Formula Request 4
Average IF? 1
Averages with conditions.... 1
Calculate average in a cell from one of two columns 2
Average of cell values - Ignore if 0 3
IF Function to return a reference instead of a value 1
Extracting the values matching to multiplr columns. 1

Back
Top