Summary

To evenly distribute a payment or other amount over a given number of months, with a variable start month, you can use a simple formula together with the AND function and a bit of boolean logic. In the example shown, the formula in E5 is:

=amount/months*AND(E4>=start,E4<(start+months))

Where amount is the named range C4, months is the named range C5, and start is the named range C6

Generic formula

=amount/months*AND(month>=start,month<(start+months))

Explanation 

At the core, this is a simple formula that simply divides the total amount by the number of months given:

=amount/months

The trick is to "cancel out" this amount in months where it doesn't apply.

To do this, we use this logical expression:

AND(E4>=start,E4<(start+months))

Here we use the AND function to test each month in row 4 to see if it's both greater than or equal to the given start month, and less than the end month, calculated by adding the start month to total months.

AND will return TRUE only when both conditions are TRUE, and return FALSE in another other case. This effectively zeros out calculations in months that fall outside the range of interest. This works because during math operations, FALSE is coerced to zero, and TRUE is coerced to 1.

Without named ranges

The formula in the example shown uses three named ranges. Without these named ranges, the formula can be written like this:

=$C$4/$C$5*AND(E4>=$C$6,E4<($C$6+$C$5))
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.