Even Spread and Formula Conversion

[Fonzie]

Hello everyone. The Intention of the formula starting is BA8 is to spread the # of Lots evening every 3 months. It works initially but then I cannot figure out why it changes to every other month. Additionally, I would like to covert this formula into a dynamic array -- not having luck with that as well. Any assistance would be greatly appreciated - thank you.

Lot Data Fields_RD.xlsx
	C	D									M			P																																					BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK
2	Monthly Spread	4
3	Lot Delivery	2
4
5
6																																																			36	37	38	39	40	41	42	43	44	45	46
7	Size (Front Footage)	# of Lots									First Take																																								12/31/24	1/31/25	2/28/25	3/31/25	4/30/25	5/31/25	6/30/25	7/31/25	8/31/25	9/30/25	10/31/25
8	40	46									12/31/2024																																								11.5	-	-	-	11.5	-	11.5	-	11.5	-	-
9	50	39									12/31/2024																																								9.8	-	-	-	9.8	-	9.8	-	9.8	-	-
10	40	47									12/31/2024																																								11.8	-	-	-	11.8	-	11.8	-	11.8	-	-
11	50	40									12/31/2024																																								10.0	-	-	-	10.0	-	10.0	-	10.0	-	-
12	40	28									8/31/2025																																								-	-	-	-	-	-	-	-	7.0	-	7.0
13	50	22									8/31/2025																																								-	-	-	-	-	-	-	-	5.5	-	5.5
14	40	29									8/31/2025																																								-	-	-	-	-	-	-	-	7.3	-	7.3
15	50	21									8/31/2025																																								-	-	-	-	-	-	-	-	5.3	-	5.3
Lot Mix Summary_Calc

Cell Formulas
Range	Formula
C8:C34	C8	=XLOOKUP($B8#,tblLotDetail[Group ID],tblLotDetail[Lot Size])
D8:D34	D8	=SUMIFS(tblLotDetail[Lots],tblLotDetail[Group ID],B8#)
M8:M34	M8	=XLOOKUP(B8#,tblLotDetail[Group ID],tblLotDetail[First Take])
BA8:BK15	BA8	=IF(AND(BA$7>=$M8,BA$7<=EDATE($M8,$D$2*$D$3),ROUND(MOD(YEARFRAC($M8,BA$7),($D$3/12)),4)=0),$D8/$D$2,"-")
Dynamic array formulas.

 

[Eric W]

I'm still a bit hazy on your setup. For example, I don't know how the D3 value fits into this, but maybe something like:

Book1
	A	B	C	D									M			P																																					BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU
1
2			Monthly Spread	4
3			Lot Delivery	2
4
5
6																																																					36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56
7			Size (Front Footage)	# of Lots									First Take																																								12/31/2024	1/31/2025	2/28/2025	3/31/2025	4/30/2025	5/31/2025	6/30/2025	7/31/2025	8/31/2025	9/30/2025	10/31/2025	11/30/2025	12/31/2025	1/31/2026	2/28/2026	3/31/2026	4/30/2026	5/31/2026	6/30/2026	7/31/2026	8/31/2026
8			40	46									12/31/2024																																								11.5				11.5				11.5				11.5
9			50	39									12/31/2024																																								9.75				9.75				9.75				9.75
10			40	47									12/31/2024																																								11.75				11.75				11.75				11.75
11			50	40									12/31/2024																																								10				10				10				10
12			40	28									8/31/2025																																																7				7				7				7
13			50	22									8/31/2025																																																5.5				5.5				5.5				5.5
14			40	29									8/31/2025																																																7.25				7.25				7.25				7.25
15			50	21									8/31/2025																																																5.25				5.25				5.25				5.25
Sheet10

Cell Formulas
Range	Formula
BA6:BU6	BA6	=SEQUENCE(,21,36)
BA7:BU7	BA7	=EOMONTH(DATE(2024,12,1),SEQUENCE(,21,0))
BA8:BU15	BA8	=IF(ISNUMBER(MATCH(BA$7#,EOMONTH(M8,SEQUENCE($D$2,,0,$D$2)),0)),D8/$D$2,"")
Dynamic array formulas.

 

[Fonzie]

I was a bit hazy as well -- I tried so many different combinations to get it to work...I forgot why I did it in the first place! In any case, your solution worked perfectly - thank you. I did not, however, expect the adjacent section to not work when I apply the dynamic array. I works as planned in row 9 but not row 8 - any idea why?

Lot Data Fields_RD.xlsx
	C	D							K	L	M	N		P																																					BA	BB																																																	CY																																			EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER
2	Monthly Spread	4
3																																																																																																					12
4
5
6																																																			36	37																																																	0																																			35	36	37	38	39	40	41	42	43	44	45
7	Size (Front Footage)	# of Lots							Escalator Start	Escalator	First Take	Base Price																																							12/31/24	1/31/25																																																	12/31/21																																			11/30/24	12/31/24	1/31/25	2/28/25	3/31/25	4/30/25	5/31/25	6/30/25	7/31/25	8/31/25	9/30/25
8	40	46							12/31/24	6.0%	12/31/2024	$68,000																																							11.5																																																		$0.0																																			$0.0	$0.0	$0.0	$0.0	$0.0	$0.0	$0.0	$0.0	$0.0	$0.0	$0.0
9	50	39							12/31/24	6.0%	12/31/2024	$85,000																																							9.8																																																		$0.0																																			$0.0	$828,750.0	$0.0	$0.0	$0.0
10	40	47							12/31/24	6.0%	12/31/2024	$60,000																																							11.8
11	50	40							12/31/24	6.0%	12/31/2024	$75,000																																							10.0
12	40	28							12/31/24	6.0%	8/31/2025	$68,000
13	50	22							12/31/24	6.0%	8/31/2025	$85,000
Lot Mix Summary_Calc (2)

Cell Formulas
Range	Formula
C8:C34	C8	=XLOOKUP($B8#,tblLotDetail[Group ID],tblLotDetail[Lot Size])
D8:D34	D8	=SUMIFS(tblLotDetail[Lots],tblLotDetail[Group ID],B8#)
K8:K34	K8	=XLOOKUP($B8#,tblLotDetail[Group ID],tblLotDetail[Escalator Start])
L8:L34	L8	=XLOOKUP(B8#,tblLotDetail[Group ID],tblLotDetail[Escalator])
M8:M34	M8	=XLOOKUP(B8#,tblLotDetail[Group ID],tblLotDetail[First Take])
N8:N34	N8	=C8#*H8#
CY6:GD6	CY6	=SEQUENCE(1,sModelTerm+1,0,1)
CY7:GD7	CY7	=DATE(YEAR(sStartDate),MONTH(sStartDate)+CY6#,0)
CY8:GD8	CY8	=(+N8+((CY$7#-K8)/(365))*L8*N8)*PRODUCT(Q8)+N("assumption: monthly compounded escalation multiplier baked into the estimated revenue calculation")
CY9,EH9:EL9	CY9	=(+$N9+((CY$7-$K9)/(365))*$L9*$N9)*PRODUCT(Q9)+N("assumption: monthly compounded escalation multiplier baked into the estimated revenue calculation")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
sModelTerm	=Setup!$F$10	CY6
sStartDate	=Setup!$F$8	CY7

 

[Eric W]

I tried pasting your latest mini-sheet, but it didn't work. Partly because of the hidden columns (I think), and partly because your formulas refer to the tblLotDetail table, which is not included in the mini-sheet.

That said, the formulas in the CY8 and CY9 cells don't look anything like what I suggested, so I have no idea what's going on there. Assuming my formula is in there somewhere, my best guess for it not working is that the dates in column M are not an exact date, there's a time component in there.

 

[Fonzie]

Ok, thanks for getting back to me -- and for all your help!