Dynamic Array Formula Problem

F

Fonzie

New Member
Joined
Nov 27, 2023
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hello everyone -- I've tried many variations of the dynamic array formula in 'AM13' but have been unsuccessful in returning any results; however, inputting the same formula into each cell starting in AM17 - without the Spill - works as expected. What am I not seeing?

Thanks in Advance.

BP_Model_Master_NEW.xlsx
BCDEFGHIJKLMNOPQXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
6Month 0Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 0Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8
712/31/20251/31/20262/28/20263/31/20264/30/20265/31/20266/30/20267/31/20268/31/20269/30/2026#########11/30/202612/31/20251/31/20262/28/20263/31/20264/30/20265/31/20266/30/20267/31/20268/31/2026
8Unit ProjectionsRevenue Projections
9Enter # of Units 936936936
10SizeSFTHMFVLTotalFFColumn1Price per FFBase PriceGross PPTrue-UpEscalator StartEscalator %First Take202520262026202620262026202620262026202620262026202520262026202620262026202620262026
1118---------21.0%6.0% 
1220---------21.0%6.0% 
1324-162--1623,888$2,000$48,000$7,776,00021.0%12/31/20256.0%3/31/2026 414141---------
1428---------21.0%6.0% 
1535---------21.0%6.0% 
1637.5---2072077,763---21.0%6.0% 
17401,343---1,34353,720$1,500$60,000$80,580,00021.0%12/31/20256.0%3/31/2026 336336336---20,443,036---20,847,039-
18501,985---1,98599,250$1,700$85,000$168,725,00021.0%12/31/20256.0%3/31/2026 496496496
1960255---25515,300$1,800$108,000$27,540,00021.0%12/31/20256.0%3/31/2026 646464
2070---------21.0%6.0% 
21Total3,952179,921$284,621,000
Lot Estimates
Cell Formulas
RangeFormula
Z6:AK6,AM6:AX6Z6=SEQUENCE(1,sModelTerm+1,0,1)
Z7:AK7,AM7:AX7Z7=DATE(YEAR(sStartDate),MONTH(sStartDate)+Z6#,0)
AM10:AX10,Z10:AK10AM10=YEAR(AM7#)
G11:G20G11=SUM(Table26[@[SF]:[VL]])
H11:H20H11=+[@Total]*[@Size]
G21G21=SUBTOTAL(109,[Total])
H21H21=SUBTOTAL(109,[FF])
AM13:AX13AM13=(K13+((AM$7#-O13)/(365))*P13*K13)*PRODUCT(Z13)+N("assumption: monthly compounded escalation multiplier baked into the estimated revenue calculation")
AM17:AU17AM17=(Table26[@[Base Price]]+((AM$7-Table26[@[Escalator Start]])/(365))*Table26[@[Escalator %]]*Table26[@[Base Price]])*PRODUCT(Z17)+N("assumption: monthly compounded escalation multiplier baked into the estimated revenue calculation")
K11:K20K11=+[@[Price per FF]]*[@Size]
Z9:AK9Z9=BYCOL(Z11:AK20,LAMBDA(_col,SUM(_col)))
Z11:AK20Z11=IF(ISNUMBER(MATCH(Z$7#,EOMONTH(Q11,SEQUENCE('Assumptions - Input'!$E$15,,0,'Assumptions - Input'!$E$15)),0)),G11/'Assumptions - Input'!$E$15,"")
L11:L20L11=+[@[Base Price]]*[@Total]
L21L21=SUBTOTAL(109,[Gross PP])
Dynamic array formulas.
Named Ranges
NameRefers ToCells
sModelTerm='Assumptions - Input'!$E$9Z6, AM6
sStartDate='Assumptions - Input'!$E$7Z7, AM7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G21Expression=$Y$3<>$G$21textNO
G11:G21Expression=$G11<>$Y11textNO
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
I'm getting errors with the formulas you provided. Looks like table26 isn't included
 
Upvote 0
From what I can tell, Table 26 appears to be a single table that includes everything shown in B10:Q21....but would be good to confirm. What is in 'Assumptions - Input'!$E$15?
 
Upvote 0
Try this...it appears the issue is with your Z array, which has blanks. So those blanks need to be converted to 0's...
Excel Formula: 
=(K11+(P11*K11*(AM$7#-O11)/365))*N(+Z11#)+N("assumption: monthly compounded escalation multiplier baked into the estimated revenue calculation")
 
Upvote 0
Solution
KRice said:
Try this...it appears the issue is with your Z array, which has blanks. So those blanks need to be converted to 0's...
Excel Formula: 
=(K11+(P11*K11*(AM$7#-O11)/365))*N(+Z11#)+N("assumption: monthly compounded escalation multiplier baked into the estimated revenue calculation")
Click to expand...
Brilliant - that was it!! Thank you very, very much :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Complex Dynamic Array Cash Flow Spread - Returns Only $0
2
Replies
16
Views
637
felixstraube
F

Forum statistics

Threads
1,224,820
Messages
6,181,160
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