Looking up next non-zero value & counting zeros (Escrow)

[TXdore]

I've looked and haven't found the answer for this. I'm trying to figure out the escrow amounts given a payment schedule, i.e. annual, quarterly, and even irregular payments

Two parts
How do you look up the NEXT non-zero (always positive) number in a row that has a series of zero's then a positive number, then look up the NEXT positive number after some more zero's, and on and on?

Then how do you count the cells from one positive number to the next, restarting after each positive number?

I've attached a file of what it should look like.

Bonus points if you don't use an array.
Also, if you have an better methodology for building an escrow table, I'll take it.

[davesexcel]

What are these bonus points , and how do we get them?

[DonkeyOte]

Using the sample file provided:

Please Login or Register  to view this content.

the Array requirement is only circumvented by embedded INDEX which makes the formula slightly less efficient but perhaps more robust.

(modify reference to $AZ as nec. this is meant to ensure all "future" data is captured within the specified range without being overly excessive in size)

[TXdore]

What are these bonus points , and how do we get them?

Pride of accomplishment. But DonkeyOte is right, it's a little more robust, but i'm only carrying the row out 60 columns or so. So the loss in efficiency is not a big deal.
We have a small firm and the guys like to see what's going on (and sometimes hardcode things) so i don't keep it locked down. None of them understand array formulas so i try to avoid them.


Thanks DonkeyOte. For some reason i couldn't get my head around it.