# Split payments over a range of numbered months (Not date ranges)

#### twilliamsli

##### New Member
[FONT=&quot]I am building a cash flow model. Standard income and expenses. the model has months 1-60. I need the expenses to me put in by range. for example taxes will be in month 13 to 33 and insurance will be in months 19 - 44. I am looking for a formula that will look up the ranges and return data in the appropriator field. any help would be great.

[/FONT]

 A B C D E F G H I J K L M N O P Annual Monthly Start Term End Term Monthly Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 1 Legal Fees 5000.00 416.67 month 1 month 1 416.67 2 Taxes 1250.00 104.17 Month 3 Month 7 104.17 3 Ins 300.00 25.00 Month 5 Month 9 25.00 4 Advance 500 41.67 Month 1 Month 4 41.67

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col span="11"></colgroup><tbody>
</tbody>

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Rick Rothstein

##### MrExcel MVP
Put this formula in cell G2 and copy it across to the last column, then copy all those cells down to the bottom of your data...

=IF(AND(G\$1>=\$D2,G\$1<=\$E2),\$F2/(MID(\$E2,7,99)-MID(\$D2,7,99)+1),"")

#### jim may

##### Well-known Member
In Column D & E I removed the text "Month" and also In your Row 1 Col G - P (to leave only numbers in those cells).

Then I enter into Cell G2 the Formula:
 =IF(AND(G\$1>=\$D2,G\$1<=\$E2),\$F2,0)

<tbody>
</tbody>
Copy Down and across

Last edited:

#### twilliamsli

##### New Member
Thank you, this is working.

#### twilliamsli

##### New Member
Thank you again, the formula is repeating for some reason. If the starting month is 2-6 - the number keep populating ... ie, Start month - Month 5 end month month 9. The formula will properly populate months 5 thru 9. then start populating again at 50.

or if start is Month 25 and End is Month 36 - it properly populated 25 to 36 - but also populated month 3 (random)

I thought it may have something to do with text formatting - so i flatten all the text to text.

here is my formula formatted for my spread sheet - do you see any reference errors? thanks

=IF(AND(I\$7>='Funding Sheet'!\$H85,I\$7<='Funding Sheet'!\$I85),\$C43/(MID('Funding Sheet'!\$I85,7,99)-MID('Funding Sheet'!\$H85,7,99)+1),0)

#### Rick Rothstein

##### MrExcel MVP
Thank you again, the formula is repeating for some reason. If the starting month is 2-6 - the number keep populating ... ie, Start month - Month 5 end month month 9. The formula will properly populate months 5 thru 9. then start populating again at 50.

or if start is Month 25 and End is Month 36 - it properly populated 25 to 36 - but also populated month 3 (random)

I thought it may have something to do with text formatting - so i flatten all the text to text.

here is my formula formatted for my spread sheet - do you see any reference errors? thanks

=IF(AND(I\$7>='Funding Sheet'!\$H85,I\$7<='Funding Sheet'!\$I85),\$C43/(MID('Funding Sheet'!\$I85,7,99)-MID('Funding Sheet'!\$H85,7,99)+1),0)
This modification will fix the problem with my formula (you can adjust it for your actual sheet and cell references)...

=IF(AND(0+MID(G\$1,7,99)>=0+MID(\$D2,7,99),0+MID(G\$1,7,99)<=0+MID(\$E2,7,99)),\$F2/(MID(\$E2,7,99)-MID(\$D2,7,99)+1),"")

Note: The problem had to do with your having included the word "Month" in all of your cells. The formula would have been much easier if you put just numbers in the cells and Custom Formatted those cells with this Type pattern...

"Month "General

That way, the actual value in the cell would have been a number, such as 23, but the cell would display "Month 23" (without the quote marks). If you had done it that way, then my formula would have been much simpler...

=IF(AND(G\$1>=\$D2,G\$1<=\$E2),\$F2/(\$E2-\$D2+1),"")

I don't know whether you can make this change though or not as I don't know if you have any other formulas or VBA code that depends on the word "Month" being in the cells or not.

Last edited:

#### jim may

##### Well-known Member
Hey guys...
Do you recognize that I am even in the room? I think NOT. Oh well, it isn't the first time. LOL!!
Jim

#### Rick Rothstein

##### MrExcel MVP
Hey guys...
Do you recognize that I am even in the room? I think NOT. Oh well, it isn't the first time. LOL!!
Jim
@Jim,

I saw your formula but interpreted the question differently than you did... but in thinking about it, your approach may have been correct, but I am not sure. Your formula simply repeated the monthly amount in the indicated monthly cells whereas I read the the OP's thread title as meaning that amount should be split across the indicated months. Also, your formula outputted 0 into cells outside of the month range rather than leave them blank (which may, in fact, be what the OP wanted as his original post was lacking in the specific output desired).

@twilliamsli,

Please read the above and clarify for us the exact output you are looking for (I am not sure anymore that what I posted is actually what you want).

Replies
4
Views
233
Replies
12
Views
385
Replies
4
Views
307
Replies
9
Views
517
Replies
0
Views
1K

1,196,017
Messages
6,012,872
Members
441,737
Latest member
bijayche

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