Forum Discussion

Copper Contributor

Jun 23, 2018

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

Formulas & Functions

tips and tricks

SergeiBaklan

Diamond Contributor

Jun 23, 2018

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.

ProRateOnQuarters.xlsx11 KB

ignatiusugwujr

Copper Contributor

Jun 23, 2018

Hi Sergei,

This is exactly what i have in mind.

Thanks for your assistance