Complex Dynamic Array Cash Flow Spread - Returns Only $0

F

Fonzie

New Member
Joined
Nov 27, 2023
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone

My original set of formulas (bottom section) works as expected; however, as you can imagine, thousands of cells containing these formulas is putting a major strain on the workbook -- therefore, I attempted to convert the original into dynamic arrays and have spent hours trying to figure out why it will not work. Any guidance would be greatly appreciated - thank you.



CF_Prep.xlsx
GHIJKLMTUVWXYZAAABACCTCUCVCWCXCYDMDNDODP
9"S-Curve" Calc Section
10Month 0Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 12Month 13Month 14Month 15
11Start MonthEnd MonthDurationMethodCost12/31/20217/31/20228/31/20229/30/202210/31/202211/30/202212/31/20221/31/20232/28/20233/31/20234/30/2023AmountStart MonthEnd MonthDurationStd. Dev12/31/20221/31/20232/28/20233/31/2023
12Month 7Month 19Month 12Straight-Line$100,200$0$0$0$0$0$0$0$0$0$0$0$100,200Month 7Month 19Month 122$0$0$0$0
13Month 16Month 28Month 12S-Curve$6,470,500$0$0$0$0$0$0$0$0$0$0$06470500Month 16Month 28Month 122$0$0$0$0
14Month 16Month 28Month 12Straight-Line$565,000$0$0$0$0$0$0$0$0$0$0$0565000Month 16Month 28Month 122$0$0$0$0
15Month 26Month 29Month 3Straight-Line$255,800$0$0$0$0$0$0$0$0$0$0$0255800Month 26Month 29Month 30.5$0$0$0$0
16Month 21Month 33Month 12Straight-Line$3,574,533$0$0$0$0$0$0$0$0$0$0$03574532.5Month 21Month 33Month 122$0$0$0$0
17
18
19
20Start MonthEnd MonthDurationMethodCostMonth 1Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 17AmountStart MonthEnd MonthDurationStd. DevMonth 13Month 14Month 15Month 16
21Month 8Month 1912 MonthsS-Curve6,065,183-29,555100,593267,938558,583911,5241,164,3981,164,398911,524558,583267,9386,065,183819122.001,164,3981,164,398911,524558,583
22Month 18Month 2912 MonthsStraight-Line5,778,000-----------5,778,0001829122.00----
23Month 13Month 2412 MonthsStraight-Line229,425------19,11919,11919,11919,11919,119229,4251324122.001,1183,80510,13521,129
24Month 13Month 2412 MonthsStraight-Line1,453,500------121,125121,125121,125121,125121,1251,453,5001324122.007,08324,10764,210133,863
25Month 13Month 2412 MonthsStraight-Line427,500------35,62535,62535,62535,62535,625427,5001324122.002,0837,09018,88539,371
Setup (2)
Cell Formulas
RangeFormula
G12:G16,CV12:CV16G12=tblCF1[Start]
H12:H16,CW12:CW16H12=tblCF1[End]
I12:I16,CX12:CX16I12=tblCF1[Length]
J12:J16J12=tblCF1[Method]
K12:K16,CU12:CU16K12=tblCF1[Cost]
M10:CR10M10=SEQUENCE(1,sModelTerm+1,0,1)
M11:CR11M11=DATE(YEAR(sStartDate),MONTH(sStartDate)+M10#,0)
M12:CR16M12=IFERROR((J12#="S-Curve")*DA12#+AND(J12#<>"S-Curve",M10#>=G12#,M10#<=H12#)*(K12#/I12#),0)
CY12:CY16CY12=IF(CX12#="","",CX12#/6)
T20:AC20,DM20:DP20T20=S20+1
CV20:CW20,CV21:CX25CV20=G20
M21:M25,T21:AC25M21=IFERROR(($J21="S-Curve")*DA21+AND($J21<>"S-Curve",M$20>=$G21,M$20<=$H21)*($K21/$I21),0)
CU21:CU25CU21=K21
CY21:CY25CY21=IF(CX21="","",CX21/6)
DM21:DP25DM21=IFERROR(AND(DM$20>=$CV21,DM$20<=$CW21)*((NORM.DIST(DM$20-$CV21+1,$CX21/2,$CY21,TRUE)-NORM.DIST(DM$20-$CV21,$CX21/2,$CY21,TRUE))/(1-2*NORM.DIST(0,$CX21/2,$CY21,TRUE))*$CU21),0)
I21:I25I21=IF(H21="","",H21-G21+1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Setup (2)'!sModelTerm='Setup (2)'!$F$8M10
'Setup (2)'!sStartDate='Setup (2)'!$F$6M11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M21:CS25Expression=#REF!="Detail"textNO
Cells with Data Validation
CellAllowCriteria
J13:J17ListDetail, Straight-Line, S-Curve
J21:J25ListDetail, Straight-Line, S-Curve
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Sorry, just realized that I didn't include a key formula in the "S-Curve" section

CF_Prep.xlsx
DADBDCDD
9
10Month 0Month 1Month 2Month 3
1112/31/20211/31/20222/28/20223/31/2022
12$0$0$0$0
13$0$0$0$0
14$0$0$0$0
15$0$0$0$0
16$0$0$0$0
Setup (2)
Cell Formulas
RangeFormula
DA10:GF11DA10=M10#
DA12:GF16DA12=IFERROR(AND(DA10#>=CV12#,DA10#<=CW12#)*((NORM.DIST(DA10#-CV12#+1,CX12#/2,CY12#,TRUE)-NORM.DIST(DA10#-CV12#,CX12#/2,CY12#,TRUE))/(1-2*NORM.DIST(0,CX12#/2,CY12#,TRUE))*CU12#),0)
Dynamic array formulas.
 
Upvote 0
Hi Again -- I know this one is a mouthful but I still cannot figure out why this won't work. Any thoughts?
 
Upvote 0
Please see below -- and it's on its own Worksheet

CF_Prep.xlsx
CDEFGHIJK
4
5Cost TypeGL CodeStart DateEnd DateStartEndLengthMethodCost
6EngineeringENG_New8/31/20228/31/202371912Straight-Line$100,200
7Lot ConstructionLC_New6/30/20236/30/2024162812S-Curve$6,470,500
8Hardscape/LandscapeHL_New6/30/20236/30/2024162812Straight-Line$565,000
9ElectricalElec_NEW3/31/20247/1/202426293Straight-Line$255,800
10Common Costs - OffsiteCCOF_New10/31/202310/31/2024213312Straight-Line$3,574,533
11
Data
Cell Formulas
RangeFormula
G6:H10G6=DATEDIF(sStartDate,E6,"m")
I6:I10I6=DATEDIF(E6,F6,"m")
Named Ranges
NameRefers ToCells
sStartDate=Setup!$F$6G6:H10
Cells with Data Validation
CellAllowCriteria
J6:J10ListDetail, Straight-Line, S-Curve
 
Upvote 0
On that table you have a custom function DATEDIF(sStartDate,E6,"m") which is not defined anywhere.
Name sStartDate refers to a Sheet called Setup which you didn't mention.

I would be easier and not as time consuming if you could upload the file
 
Upvote 0
I really appreciate you taking the time to assist! I don't believe we are permitted to upload a file anymore. Do you have an alternate preference?
 
Upvote 0
You can upload the file to a cloud storage (like google drive, dropbox, etc) and share the link here. That is not prohibited as far as I know.
 
Upvote 0
I included a link to a OneDrive folder because the dynamic array formulas may not render properly because of Google Sheets

CF_Prep.xlsx

1drv.ms 1drv.ms
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

Share this page

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