rolling months sumproduct installments

[wgoytr]

hey guys,

im trying to calculate the sum of installments according to the number of unit sales

for instance: everytime a cell in column B is > 0, the formula in column C should fill the next two rows with a value of 1

B:
0
1
1
0
0

C:
0
1
2
1
0

ive uploaded an example sheet with what im trying to accomplish!

any help is appreciated

 

[Hui]

C10: =SUM(B9:B10)*$C$6
Copy down

 

[wgoytr]

C10: =SUM(B9:B10)*$C$6
Copy down

i ran into a problem when modifying the formula:

any cells above and outside of the table will be considered :/

for instance, if my table starts at C100 and i have cells above with numbers, the formula will consider the numbers as if it where part of the table.

=sum(C100 : offset(C100,-"number_of_installments",) )

…

 

[Hui]

=sum(offset(C100,-"number_of_installments",0) )

 

[p45cal]

I hope you were only after the payments column and that the green shaded columns of your sheet were just your helper columns.
In the attached you can vary car price, # of instalments and numbers of unit sales.
If you have unit sales at the bottom of the table the results table will expand to include the necessary extra months to complete payment.
The only drawback is that once you've changed those input parameters you need to Refresh the green results table like you do a pivot table, which you can do in several ways but one way is to right-click the results table and choose Refresh.

It's a Power Query solution, and behind the scenes it's not very elegant (too many steps) because I was also trying to reproduce your green shaded columns but I got lazy.