average(if()) question

[agpj]

I have a list similar to the following in column A:

A
98
15
0
315
223
145
62
0
329
216
101
98
0
310
198
96

As you can see, the numbers jump and wane cyclically. I want to find the averages of all the "jumps." In this case, the average of 315, 329, and 310. Is there a way I can write an average-if function (preferrably sans VBA) that averages all the numbers after every instance of 0?

Thanks
pj

 

[njimack]

=AVERAGE(IF(A1:A15=0,A2:A16))
(confirmed with ctrl+shift+enter)

 

[Jonmo1]

Perhaps

=AVERAGE(IF(A2:A100>A1:A99,A2:A100)) 'Notice the offset - it's intentional.

CTRL + SHIFT + ENTER

 

[Aladin Akyurek]

What are the desired outcomes?

 

[agpj]

njimack and jonmo1,

your solutions worked perfectly, thanks! I don't understand the logic behind your formulas though...would you mind running through it? Are the ranges basically for-loop equivalents?

 

[Jonmo1]

njimack and jonmo1,

your solutions worked perfectly, thanks! I don't understand the logic behind your formulas though...would you mind running through it? Are the ranges basically for-loop equivalents?

That's an interesting way to look at it, yes it does do a kind of loop.

Both our formulas are essentially the same, but using different criteria.

njimack used =0, where I used Greater Than..

Use njimack's if it's always resetting to 0
Use mine if it's more like when the number goes down any amount, instead of up.

So taking njimack's
=AVERAGE(IF(A1:A15=0,A2:A16))

It does do a loop of every cell in A1:A15.
But it's not technically using a Row#, it's using the POSITION # in the array A1:A15.

So first loop looks at the first position of A1:A15 (A1).
Does that = 0, if it is, it then adds the value in the first position # of A2:A16 (A2)

2nd loop looks at the 2nd position of the array A1:A5 (A2)
Does that = 0, if it is then it adds the value of the 2nd postion of A2:A16 (A3)

And so on.

Hope that helps..

 

[agpj]

Makes perfect sense, thank for your help!