Counting number of zeros between two values

[BWellman]

Hello,

I have a file where I am looking to amortise rent payments across weeks between rent payments. To do that, I need to count the number of "0" cells between two rent payments, and divide the rent value by that amount. For example in the attached document, the formula would find the first $660, and divide that by three (c:e), then it would find the second 660, divide that by 3 as well (f:h), the third 660, divide that by 4 (i:l) etc..

A few other points, some months rent are missed so the number of "0" between each rent payment won't be the same. Rent payments aren't always the same.

Cheers,
B

[AliGW]

Please manually fill in your expected outcomes and re-post the workbook.

[BWellman]

I have attached the desired outcome here:

[Phuocam]

Try in C4:

=IF(AND(B3<>0,C3=0),INDEX(C3:$T$3,MATCH(TRUE,INDEX(C3:$T$3<>0,0),0))/MATCH(TRUE,INDEX(C3:$T$3<>0,0),0),B4)

[samba_ravi]

or
c4=IF(OR(COLUMNS($C4:C4)=1,AND(B3>0,C3=0)),INDEX(C3:$T3,MATCH(1,INDEX((C3:$T3>0)+0,0),0))/MATCH(1,INDEX((C3:$T3>0)+0,0),0),IF(B3=0,B4,C3))
Try this and copy towards right

[stax290]

Hi all,

I need a formula to help with calculating a drop down box...

I want to use this to help quoting works, I have created a drop down box numbered 1-8 and need it so that when 1 is selected it is worth 500... Screen Shot 2017-08-21 at 22.00.00.png
I will have this for multiple categories, then a box that calculates all for a final figure at the end...

If this even possible?

Any help greatly appreciated!!

[AliGW]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.