Averaging last 25 non zero values.

  • Thread starter Thread starter peterv
  • Start date Start date
P

peterv

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
 
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
 
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
Click to expand...

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)))
 

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 non contigous data to also exclude zeros 14
Average non-adjacent cells & exclude zeros 3
Calculate Average for same period last year 3
Need help (Average excluding zero values) 2
Average of a string containing multiple values 20
Average over non-existing numbers 7
average of different cells ignoring zero values 1
output row number for corresponding non-zero value of match functi 2

Back
Top