Dynamic sum repeat every 12 columns based on an array

gtirrell

New Member
Joined
Jun 16, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I want this formula to add costs (from column Assumptions!H60:H649) in between a start date/month (Assumptions!J60:J649) and end date/month (Assumptions!L60:L649) that correspond to a certain cost category (the category can be picked in Assumptions!C60:C649, but they are added into a summary row with the category listed in Accruals!$B$85 for example). Monthly dates on the Accruals sheet are spread horizontally across D5:BK5 (60 months), so this formula will be dragged to the right (also down for some other categories).

The Mapping reference is to how often this will repeat. This mapping reference is for an "annual" repeat, I also have a "quarterly" repeat in this formula that just replaces the 12 with a 3. There's probably a more elegant way to do that part but this works.

My issue is that I know MONTH(Assumptions!$J$64) (bolded below) needs to reference J60:J649 so all the costs that should start in a particular month are included in this month's cell (Accruals!D85 in this example), but I'm not sure how to make that part of the formula work with that array.

=...+IFERROR(IF(MOD(MONTH(Accruals!D$5)-MONTH(Assumptions!$J$64),12)=0,INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0),0)*IFERROR((MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0)),0)
 
Ok, thanks…that mystery is solved. That behavior is as I intended. Now to fix it… First, what would you expect if a user enters a 7 month period for Quarterly cost, say 1/1/2020 to 7/1/2020? Would you expect to see 2 or 3 table entries? Extending the logic you’ve described, I would guess 3, but the formula will interpret this scenario as 2. This issue is what led to my suggestion to use Data Validation on the date inputs to limit dates only to those that are some monthly/quarterly/annual multiple of the start date, depending on which frequency is selected.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
My last post didn't quite make my point. Let me try again. What would you expect if a user enters a 6-month period for some Quarterly expense, say 1/1/2020 to 6/1/2020 (I'm assuming that entering 6/1/2020 means anytime in June 2020)? The 1st would be 1/1/2020, the 2nd on 4/1/2020 (which would cover through June 2020, so I'm assuming you would expect only 2 costs/payments to show).

Then suppose another line item covers an 8-month period from 1/1/2020 to 8/1/2020 and costs are also Quarterly. The 1st would be 1/1/2020, the 2nd on 4/1/2020, and the 3rd would be on 7/1/2020. The last payment seemingly should cover a full quarter through Sept 2020 (until 10/1/2020), but the "end date" terminates at 8/1/2020. I'm assuming you would expect 3 costs/payments to show in this case. This quarterly example is similar to and would be consistent with what you've described as an expectation for the annual payments where a 13-month period is specified. The last payment can either fall on the end date or it can fall anywhere inside the last period covered by the end date, even if that last period extends well beyond the end date.

I think the fix for this is to change the "end" formula so that it looks at the number of month intervals between start and end dates and then rounds that number of monthly intervals up to the nearest multiple number of months consistent with the frequency (so nearest multiple of 3 for quarterly, or 12 for annual). Then that "ceiling" amount represents the total number of monthly intervals covered by some integer number of payment intervals. Finally, this total number of monthly intervals is added to the start index to determine the index of the last payment. In the formula below, I made the adjustment in the "freq" definition from column 2 to column 4 as you previously indicated, and then the "end" definition is changed as just described. There are no other changes.
Excel Formula:
=LET(cat,Assumptions!$C$60:$C$649, data,Assumptions!$H$60:$L$649, daterng,$D$5:$BK$5,
          fcat,FILTER(data,cat=$B84),
          amt,INDEX(fcat,,1),
          start,MATCH(INDEX(fcat,,3),daterng,0),
          endidx,MATCH(INDEX(fcat,,5),daterng,0),
          freq,XLOOKUP(INDEX(fcat,,4),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}),
          end,start+CEILING(endidx-start,freq),
          ma,MAKEARRAY(COUNT(amt),60,LAMBDA(r,c,IF(c<INDEX(start,r),0,IF(c>INDEX(end,r),0,IF(MOD(c-INDEX(start,r),INDEX(freq,r))=0,INDEX(amt,r),0) )))),
          IFERROR(BYCOL(ma,LAMBDA(column,SUM(column))),SEQUENCE(,60,0,0) ) )
Let me know if this adjustment produces the expected results or if you discover any other issues. In my tests, those quarterly and annual end dates that do not cover an entire 3-month or 12-month period are now being counted rather than ignored, but I'm curious if you run into any instances where this is not the case.

Here are some bottom-of-table formulas that include this adjustment if you want to investigate in more detail:
MrExcel_20220628.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAA
101123456789101112131415
102CategoryAmtStart DateFreqEnd DatestartEnd Date idxend1/1/202/1/203/1/204/1/205/1/206/1/207/1/208/1/209/1/2010/1/2011/1/2012/1/201/1/212/1/213/1/21
103c cat3202/1/2020Monthly3/1/202222727022222222222222
104704/1/2020Once4/1/2020444000700000000000
105605/1/2020Quarterly1/1/202353738000060060060060
1065.501/1/2020Annual1/1/2021113135.5000000000005.500
1075.501/1/2020Annual1/1/2021113135.5000000000005.500
1085.501/1/2020Annual1/1/2024149495.5000000000005.500
Accruals
Cell Formulas
RangeFormula
D103:H108D103=FILTER(Assumptions!$H$60:$L$649,Assumptions!$C$60:$C$649=$B103)
I103:I108I103=MATCH(INDEX($D103#,,3),$D$5:$BK$5,0)
J103:J108J103=MATCH(INDEX($D103#,,5),$D$5:$BK$5,0)
K103:K108K103=I103#+CEILING(J103#-I103#,XLOOKUP(INDEX(D103#,,4),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}))
M101:BT101M101=SEQUENCE(,60)
M102:BT102M102=DATE(2020,SEQUENCE(1,60),1)
M103:BT108M103=MAKEARRAY(COUNT(INDEX(D103#,,1)),60,LET(start,I103#, freq,XLOOKUP(INDEX(D103#,,4),{"annual";"quarterly";"monthly";"once"},{12;3;1;1}), end,I103#+CEILING(J103#-I103#,freq), LAMBDA(r,c,IF(c<INDEX(start,r),0,IF(c>INDEX(end,r),0,IF(MOD(c-INDEX(start,r),INDEX(freq,r))=0,INDEX(INDEX(D103#,,1),r),0) )))))
Dynamic array formulas.
 
Upvote 0
Solution
Amazing! Works perfectly now, and it did before, now just as I pictured. Thanks so much again for all the time you have put in to solving this, and helping me learn a lot about how the formulas work along the way.
Cheers,
G
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top