Calculating timelines

[djjons]

Hi

Is there anyway of setting up a formula that knows when to stop calculating? For example I have a cost of $150 that I want payed over a variable number of months from 1 to 6. Can I set up a formula that shows the cost per month i.e $150 divided by the number of months and then stops calculating when the $150 is reached?

Thanks!

 

[lenze]

Hi and welcome to the board!!!

A few questions.
What cell will contain the cost ($150)?
What cell gives the number of months?
Do you want to drag across showing the balance for each month?
lenze

 

[djjons]

Hi Lenze

Thanks for your welcome and your quick response! The cost is in cell D31 which is a dropdown menu containing the choice of $150 and $180 and the number of months (0-6) is in cell D34.

Yes,I would like to drag the cost across and to end when the total cost is finished

 

[lenze]

Say you want to drag across from E31?

=IF(D31-$D31/$D34>=0,D31-$D31/$D34,"")

lenze

 

[djjons]

Thanks but this doesn't seem to work.

I want to calculate it over 12 months and for it to stop when the amount is finished. So for example if I selected $180 and then 6 months it would show $30 for six months and then stop,is this possible?

 

[lenze]

So you want to show the amount, not the balance?

=IF(D31-$D31/$D34>=0,$D31/$D34,"")

lenze

 

[djjons]

Right,I am trying to show the amount over six months and then for it to stop when the total has finished so in my example six months of $30 and then nothing

 

[djjons]

Thanks,but the formula doesn't drag,it just seems to calculate in one cell only

 

[lenze]

OK For E31, try

=IF($D31-($D31/$D34)*(COLUMN()-4)>=0,$D31/$D34,"")

and drag across

lenze

 

[djjons]

Yes,it works!

Many thanks for your time and help.