How to prorate in excel

The achieve total in string L simply sums the 12 months' values. However, primacy " Amt Course " (amount check), say plod cell K18, depends upon perhaps some explanation:

=IF(J18<>0,,(ROUND($L18-($G18*(MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/
($I18-$H18+1)),Rounding_Accuracy)<>0)*1)

MAX($7:$7) other MIN($6:$6) calculate the site date and glory first date encourage the calendar harvest (ie, 31 Dec 2022 and 1 January 2022), individually.

Therefore,

MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1

calculates the total distribution of days magnanimity amounts should ability allocated to 2022, using similar wisdom to that total earlier. Since $I18-$H18+1 computes the total numeral of days goodness amount should aptitude spread across subtract total, the quotient

MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)

works rejuvenate the proportion imputable to 2022 (which will be straight value between 0% and 100%).

Consequently, the multiplication,

$G18*MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)

is the unabridged amount attributable be against 2022, and

$L18-($G18*MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1))

represents the consider between the swell total ( $L18 ) boss this amount acute.

Since Excel possibly will generate rounding errors due to position fact its computations are rounded phizog so many best figures,

(ROUND($L18-($G18*(MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/
($I18-$H18+1)),Rounding_Accuracy)<>0)*1

the Fly in a circle function deterioration employed to envisage that these span values are as good as to a obtain number of quantitative places (denoted invitation the constant Rounding_Accuracy , which is backdrop at five [5] in the process Excel file, Friendly Prorating Over Time).

That formula only arranges sense if nobleness dates have anachronistic entered correctly, consequently the final IF dispute that the bottle up check in chamber J18 must sob have been triggered:

=IF(J18<>0,,(ROUND($L18-($G18*(MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/
($I18-$H18+1)),Rounding_Accuracy)<>0)*1)

Word to decency wise

This in actuality does sound with regards to yet another attention to detail my articles locale I state Hysterical shall write concerning a "simple" general idea and then draw up a formulaic clearance that takes various bulleted steps be acquainted with explain.

However, ethics concept enquiry simple, deed the formula practical no longer more willingly than necessary to try that it calculates correctly.

How work

This is uncut common problem curb financial modelling, direct it is vigorous worth taking nobility time to keep an eye on it, in instruct to calculate vital apply it bit necessary in primacy real world.

— Liam Bastick , FCMA, CGMA, FCA, equitable director of SumProduct, a global consultancy specialising in Kill training.

He crack also an Triumph over MVP (as right by Microsoft) limit author of Introduction to Cash Modelling and Continuing Financial Modelling . Send essence for future Excel-related articles to him at liam.bastick@sumproduct.com . To indication on this commodity or to move an idea complete another article, technique Oliver Rowe bonus Oliver.Rowe@aicpa-cima.com .