Prorating savings based on number of months in a quarter

Obainoluciano

New Member
Joined
Jun 23, 2018
Messages
4
Hi All
i need with a formula to do the following calculation
1 determine the quarter that a date falls in and then compare it to the last quarter of the year.
2. Determine the number of months in each quarter and then prorate an amount based on the number of month in each quarter.

for example, if I have a date of June 5, the month falls into quarter 2 and when compared to the end of the year, I have 3 quarters - quarter 2 ( 1 month), quarter 3(3 months) and quarter 4 ( 3 month). I would like to pro-rate an amount ($1245) into 3 different columns based on the number of months in that quarter.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
But June has 7 months remaining, or 3 quarters inclusive. Which do you want? Here's quarters:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">6/5/2018</td><td style="text-align: right;;">1245</td><td style="text-align: right;;">415</td><td style="text-align: right;;">415</td><td style="text-align: right;;">415</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=IF(<font color="Blue">COLUMN(<font color="Red">A1</font>)>1+INT(<font color="Red">(<font color="Green">2+MONTH(<font color="Purple">$A1</font>)</font>)/3</font>),"",$B1/(<font color="Red">1+INT(<font color="Green">(<font color="Purple">2+MONTH(<font color="Teal">$A1</font>)</font>)/3</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

oh wait I see, hang on.......
 
Last edited:

Obainoluciano

New Member
Joined
Jun 23, 2018
Messages
4
But June has 7 months remaining, or 3 quarters inclusive. Which do you want? Here's quarters:

Excel 2010
ABCDE
16/5/20181245415415415

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
C1=IF(COLUMN(A1)>1+INT((2+MONTH($A1))/3),"",$B1/(1+INT((2+MONTH($A1))/3)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



oh wait I see, hang on.......
of the 7 months between june to december, i would want column C to be 1/7*1245 =177.86, column D = (3/7)*1245 =533.57 and column E = (3/7)*1245 =533.57. Column C label will be Q2, D - Q3 and E-Q4
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
This?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">6/5/2018</td><td style="text-align: right;;">1245</td><td style="text-align: right;;">177.8571</td><td style="text-align: right;;">533.5714</td><td style="text-align: right;;">533.5714</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=IF(<font color="Blue">COLUMN(<font color="Red">A1</font>)=1,(<font color="Red">3-MOD(<font color="Green">MONTH(<font color="Purple">$A1</font>)-1,3</font>)</font>)*(<font color="Red">$B1/(<font color="Green">13-MONTH(<font color="Purple">A1</font>)</font>)</font>),IF(<font color="Red">COLUMN(<font color="Green">A1</font>)>5-(<font color="Green">INT(<font color="Purple">(<font color="Teal">MONTH(<font color="#FF00FF">$A1</font>)+2</font>)/3</font>)</font>),"",3*$B1/(<font color="Green">13-MONTH(<font color="Purple">$A1</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,124
Messages
5,466,828
Members
406,501
Latest member
TheoDoc

This Week's Hot Topics

Top