A reader sent me an interesting formula problem this week, so thought I'd share it as a formula challenge. The problem is this:
You have a fixed monthly payment, a start date, and a given number of months. What formula can you use to sum total payments by year, based on the following worksheet:
In other words, what formula works in E5, copied across to I5, to get a sum for each year shown?
I came up with a formula myself, but I'd love to see your ideas, too. If you're interested, leave a comment with the formula you propose.
You can use the following named ranges in your formula if you like: mos (C5), amount (C6), start (C7), end (C8).
You can download the worksheet below.
So many great formulas! Thanks to everyone who took the time to submit an answer. Below are my rambling thoughts on the problem and some of the solutions below.
Note: I never clarified how month boundaries should be handled. I was just following another worksheet as an example. The key information is 30 payments, starting March 1: 10 payments in 2017, 12 payments in 2018, and 8 payments (the balance) in 2019.
So, if you are struggling to understand how you might try to solve a problem like this, focus first on payments. Once you know how many payments are in a year, you can just multiply that number by the amount and you're done.
So, how might you find the number of payments in a given year? In the comments below you'll find lots of good ideas. There are several patterns I noticed, and I've listed a few below. This is a work in progress...
Design patterns
IF + AND/OR + YEAR + MONTH
IF is a trusty standby in so many formulas, and it's used in many of the suggested formulas to figure out if year of interest is "in bounds" of the start and end dates. In many cases, IF is combined with OR or AND to keep formulas compact.
IFERROR + DATEDIF + MAX + MIN
DATEDIF can return the difference between two dates in months, so the idea here is to use MAX and MIN (for brevity, instead of IF) to calculate a start date and end date for each year, and let DATEDIF get the months between. DATEDIF throws a #NUM error when the start date is not less than the end date, so IFERROR is used to catch the error and return zero. See formulas by 闫强, Arun, and David below.
MAX + MIN + YEAR + MONTH
Robert and Peter's formulas do almost all the work with MAX and MIN, with no IF in sight. Amazing. If the idea of using MAX and MIN to replace IF is new to you, this article explains the concept.
DAYS360
The Excel DAYS360 function returns the number of days between two dates based on a 360-day year. It's a way of calculating months based on the idea that every month has 30 days.
SUM + DATE
This is my inefficient (but elegant!) approach using the DATE function and an array constant with a number for each month. the DATE function spins up a date for each month of a year using an array constant, and boolean logic is used to check overlap.
