Forum Discussion
Need help with formula to calculate prorated quarterly savings.
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.
Hi,
For the data structured like this
the formula in C2 could be
=IF(3*RIGHT(C$1,1)-MONTH($A2)+1<0,0,IF(3*RIGHT(C$1,1)-MONTH($A2)+1>3,3,3*RIGHT(C$1,1)-MONTH($A2)+1))*$B$2/(13-MONTH($A2))
and drag it to other cells.
Please check attached.
Hi Sergei,
This is exactly what i have in mind.
Thanks for your assistance
