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

[twilliamsli]

[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]
[TABLE="width: 1248"]
<colgroup><col><col><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Annual [/TD]
[TD]Monthly[/TD]
[TD]Start Term [/TD]
[TD]End Term[/TD]
[TD]Monthly[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Month 7[/TD]
[TD]Month 8[/TD]
[TD]Month 9[/TD]
[TD]Month 10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]Legal Fees[/TD]
[TD]5000.00[/TD]
[TD]416.67[/TD]
[TD]month 1[/TD]
[TD]month 1 [/TD]
[TD]416.67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Taxes[/TD]
[TD]1250.00[/TD]
[TD]104.17[/TD]
[TD]Month 3[/TD]
[TD]Month 7[/TD]
[TD]104.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]Ins[/TD]
[TD]300.00[/TD]
[TD]25.00[/TD]
[TD]Month 5[/TD]
[TD]Month 9[/TD]
[TD]25.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]Advance[/TD]
[TD]500[/TD]
[TD]41.67[/TD]
[TD]Month 1[/TD]
[TD]Month 4[/TD]
[TD]41.67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

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]

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: [TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl63, width: 72"]=IF(AND(G$1>=$D2,G$1<=$E2),$F2,0)[/TD]
[/TR]
</tbody>[/TABLE]
Copy Down and across

 

[twilliamsli]

Thank you, this is working.

 

[twilliamsli]

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]

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.

 

[jim may]

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]

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).