# Calculating timelines

#### djjons

##### New Member
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!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### lenze

##### Legend
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

##### New Member
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

##### Legend
Say you want to drag across from E31?
Code:
``=IF(D31-\$D31/\$D34>=0,D31-\$D31/\$D34,"")``

lenze

#### djjons

##### New Member
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

##### Legend
So you want to show the amount, not the balance?
Code:
``=IF(D31-\$D31/\$D34>=0,\$D31/\$D34,"")``
lenze

#### djjons

##### New Member
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

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

#### lenze

##### Legend
OK For E31, try
Code:
``=IF(\$D31-(\$D31/\$D34)*(COLUMN()-4)>=0,\$D31/\$D34,"")``
and drag across

lenze

#### djjons

##### New Member
Yes,it works!

Many thanks for your time and help.

Replies
5
Views
389
Replies
2
Views
264
Replies
1
Views
573
Replies
4
Views
356
Replies
4
Views
804

1,191,191
Messages
5,985,209
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back