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:
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:
The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...
In this video, we build two formulas to calculate the company match for a 401k contribution. This is a great example of building a complex formula with several nested IFs in small steps.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.