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
 
Sort by date Sort by votes
I think you problem is the AND function. The logical functions like AND and OR are not array friendly.
So you have to replace your AND Function with a multypling opreation with in logical terms is the same.

So you replace AND(A1:A4 = "a", B1:B4="b") with (A1:A4="a")*(B1:B4="b")

So your formula in DA12 will become:

Excel Formula: 
=IFERROR(($DA$10# >= $CV$12#) * ($DA$10# <= $CW$12#) * ((NORM.DIST($DA$10# - $CV$12# + 1, $CX$12# / 2, $CY$12#, TRUE) - NORM.DIST($DA$10# - $CV$12#, $CX$12# / 2, $CY$12#, TRUE)) / (1 - 2 * NORM.DIST(0, $CX$12# / 2, $CY$12#, TRUE)) * $CU$12#), 0)

And your formula in B12:

Excel Formula: 
=IFERROR(($J$12# = "S-Curve") * DA12# + (($J$12# <> "S-Curve") * ($M$10# >= $G$12#) * ($M$10# <= $H$12#)) * ($K$12# / $I$12#), 0
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Almost there!! The first formula (S-Curve) worked perfectly. The second formula, however, is returning some interesting results. The goal is for it to spread the values in column CU accordingly. One last thing - both formula's are adding one more month of $spread. In other words, if the duration = 12 months, it's spreading it over 13 months. Thanks again for taking the time....


CF_Prep.xlsx
CUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQ
10Month 0Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16
11CostStart MonthEnd MonthDurationSD12/31/20211/31/20222/28/20223/31/20224/30/20225/31/20226/30/20227/31/20228/31/20229/30/202210/31/202211/30/202212/31/20221/31/20232/28/20233/31/20234/30/2023
12$100,200Month 7Month 19Month 122$0$0$0$0$0$0$0($180,365)($613,881)($1,635,130)($3,408,841)($5,562,718)($7,105,921)($7,105,921)($5,562,718)($3,408,841)($1,635,130)
13$6,470,500Month 16Month 28Month 122$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0($11,647,222)
14$565,000Month 16Month 28Month 122$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0($1,017,028)
15$255,800Month 26Month 29Month 30.5$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
16$3,574,533Month 21Month 33Month 122$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
Setup
Cell Formulas
RangeFormula
CU12:CU16CU12=tblCF1[Cost]
CV12:CV16CV12=tblCF1[Start]
CW12:CW16CW12=tblCF1[End]
CX12:CX16CX12=tblCF1[Length]
CY12:CY16CY12=IF(CX12#="","",CX12#/6)
DA10:GF11DA10=M10#
DA12:GF16DA12=IFERROR((DA10#>=CV12#)*(DA10#<=CW12#)*((NORM.DIST(DA10#-CV12#+1,CX12#/2,CY12#,TRUE)-NORM.DIST(DA10#-CV12#,CX12#/2,CY12#,TRUE))/(DQ121-2*NORM.DIST(0,CX12#/2,CY12#,TRUE))*CU12#),0)
Dynamic array formulas.
 
Upvote 0
Previous formula was:
Excel Formula: 
=IFERROR((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)

Formula you posted in post 12 is:

Excel Formula: 
=IFERROR((DA10#>=CV12#)*(DA10#<=CW12#)*((NORM.DIST(DA10#-CV12#+1,CX12#/2,CY12#,TRUE)-NORM.DIST(DA10#-CV12#,CX12#/2,CY12#,TRUE))/(DQ121-2*NORM.DIST(0,CX12#/2,CY12#,TRUE))*CU12#),0)

I guess the DQ121-2* is a typo. First of all because you dont have any value in DQ121 and second it was 1-2* the DQ12 i guess is the error. Can you check?
 
Upvote 0
Beautiful!! There were actually two typo's - how embarrassing. Would you happen to know why it's spreading the extra month?

CF_Prep.xlsx
CUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDT
10Month 0Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 17Month 18Month 19
11CostStart MonthEnd MonthDurationSD12/31/20211/31/20222/28/20223/31/20224/30/20225/31/20226/30/20227/31/20228/31/20229/30/202210/31/202211/30/202212/31/20221/31/20232/28/20233/31/20234/30/20235/31/20236/30/20237/31/2023
12$100,200Month 7Month 19Month 122$0$0$0$0$0$0$0$488$1,662$4,426$9,228$15,059$19,236$19,236$15,059$9,228$4,426$1,662$488$112
13$6,470,500Month 16Month 28Month 122$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$31,530$107,315$285,843$595,912
14$565,000Month 16Month 28Month 122$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$2,753$9,371$24,960$52,035
15$255,800Month 26Month 29Month 30.5$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
16$3,574,533Month 21Month 33Month 122$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
Setup
Cell Formulas
RangeFormula
CU12:CU16CU12=tblCF1[Cost]
CV12:CV16CV12=tblCF1[Start]
CW12:CW16CW12=tblCF1[End]
CX12:CX16CX12=tblCF1[Length]
CY12:CY16CY12=IF(CX12#="","",CX12#/6)
DA10:GF11DA10=M10#
DA12:GF16DA12=IFERROR((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
Change
Excel Formula: 
($DA$10# >= $CV$12#) * ($DA$10# <= $CW$12#)
(which will include month from 7 to 19 in the first example, that is 13 month (19-)

to
Excel Formula: 
($DA$10# >= $CV$12#) * ($DA$10# < $CW$12#)
so the last month 19

so your formula will be:

Excel Formula: 
=IFERROR(($DA$10# >= $CV$12#) * ($DA$10# < $CW$12#) * ((NORM.DIST($DA$10# - $CV$12# + 1, $CX$12# / 2, $CY$12#, TRUE) - NORM.DIST($DA$10# - $CV$12#, $CX$12# / 2, $CY$12#, TRUE)) / (1 - 2 * NORM.DIST(0, $CX$12# / 2, $CY$12#, TRUE)) * $CU$12#), 0)

Same in the other formula:
Excel Formula: 
=IFERROR(($J$12# = "S-Curve") * DA12# + (($J$12# <> "S-Curve") * ($M$10# >= $G$12#) * ($M$10# < $H$12#)) * ($K$12# / $I$12#), 0)

Or, you could change the End formula in your tblCF1 table and subtract 1.
 
Upvote 0
Solution
You must log in or register to reply here.

Forum statistics

Threads
1,215,076
Messages
6,122,983
Members
449,092
Latest member
Mr Hughes

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