quarterly calcs from month numbers

[ringojb]

I've been asked to calculate quarterly dollar spread based on start and end months.

I need it in this format.

removed bad image

Q1 calc is easy, but gets complicated (for me) on the rest. Any help would be greatful. Thank you.

[TABLE="width: 402"]
<tbody>[TR]
[TD="width: 79, bgcolor: transparent"]start month[/TD]
[TD="width: 75, bgcolor: transparent"]end month[/TD]
[TD="width: 127, bgcolor: transparent"]total cost[/TD]
[TD="width: 64, bgcolor: transparent"]q1[/TD]
[TD="width: 64, bgcolor: transparent"]q2[/TD]
[TD="width: 64, bgcolor: transparent"]q3[/TD]
[TD="width: 64, bgcolor: transparent"]q4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]500000[/TD]
[TD="bgcolor: transparent"]calc[/TD]
[TD="bgcolor: transparent"]calc[/TD]
[TD="bgcolor: transparent"]calc[/TD]
[TD="bgcolor: transparent"]calc[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]need these results[/TD]
[TD="bgcolor: transparent, align: right"]100000[/TD]
[TD="bgcolor: transparent, align: right"]300000[/TD]
[TD="bgcolor: transparent, align: right"]100000[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

See if the following formula works for you:

=$C2/($B2-$A2+1)*MAX(0,MIN($B2,RIGHT(D$1)*3)-MAX($A2,RIGHT(D$1)*3-2)+1)

 

[ringojb]

See if the following formula works for you:

=$C2/($B2-$A2+1)*MAX(0,MIN($B2,RIGHT(D$1)*3)-MAX($A2,RIGHT(D$1)*3-2)+1)

Thank you! This looks like it is going to work.