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.

start month	end month	total cost	q1	q2	q3	q4
3	7	500000	calc	calc	calc	calc
		need these results	100000	300000	100000

<tbody>
</tbody>

 

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