Help on cumulative summation with drop off after 36 months

**amanbajaj** · 02-27-2019, 04:00 PM

Hey excel whiz kids,

I'm trying to create a monthly lease expense schedule for iPad purchases where the purchases occur almost every month. The lease will be for 36 months. I'd like to have my cumulative lease payments calculate in just one line, rather than create 36 individual payment lines. I am trying to accomplish this by using a cumulative summation as shown in the attached excel. My issue is how do I make the first months payment drop off after 36 months, then have the first two months payments drop off after 37 months.....so on and so forth. I have been looking around for a solution but have not been able to figure one out. Any help will be greatly appreciated. I have attached the excel that explains the issue and shows my calculations.

Thanks in anticipation

Aman

**Aardigspook** · 02-27-2019, 05:32 PM

Welcome to the forum.

This should do it for you - enter in D14 and drag along:

Formula:

Please Login or Register  to view this content.

What it does is check if there's a number in the column 35 to the left of the reference (D12, then D13 as you drag, and so on). If so, it will sum from that column to the reference - so 36 columns in total. If not, it will sum from column D to the reference.

A possible alternative might be this:

Formula:

Please Login or Register  to view this content.

What this one does is attempt to sum to the reference cell (D12, E12, and so on) from 35 columns before (so 36 in total). If that generates an error, then it sums from $D12 to the reference cell. A quick check seems to show that this gives the same results, but I'm not sure how it's handling the blank cell of C12, so I would recommend the previous formula instead, just in case.

Hope that helps.

**amanbajaj** · 02-27-2019, 06:18 PM

Thanks Much for the help. While I was waiting for some response, I came up with a formula as well:

SUM(INDEX($D$10:$CC$10,,IF((E7-$D$7)<36,1,(E7-36+1))):E10)

But looks like your formula is much more elegant as for mine to work, I have to number the columns in row seven starting with D7 as 1.

Regards,
Aman

**Aardigspook** · 02-28-2019, 07:25 AM

You're welcome.

If you keep the numbers in row 7 then another option is to put the below formula in AM14 then drag right and left - it'll get lots of errors in the formulae as you drag it left, which is why you have to start in column AM.

Formula:

Please Login or Register  to view this content.

Help on cumulative summation with drop off after 36 months

LinkBack

Thread Tools

Rate This Thread

Display

Help on cumulative summation with drop off after 36 months

Re: Help on cumulative summation with drop off after 36 months

Re: Help on cumulative summation with drop off after 36 months

Re: Help on cumulative summation with drop off after 36 months

Thread Information

Users Browsing this Thread

Similar Threads

Progressive/Cumulative summation and % Calculation

Cumulative summation

[SOLVED] Dynamic cumulative SUM RANGE - different months

[SOLVED] summation of months data

Cumulative summation

Cumulative total on months by Agent

Cumulative months between dates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions