Real estate calculation

[excelzrf]

Hello,

I am looking for a formula that will allow us to automatically fill in the sales of units for a given assumption.

The assumption is that we will sell 2 houses every other month. I have these as drivers of:
2 sales
Every 2 months

I need it to populate the cells automatically. I need it to be:
1/1/23 - 0 sales
2/1/23 - 2 sales
3/1/23 - 0 sales
4/1/23 - 2 sales

and so on. How can I accomplish this?

 

[Peter_SSs]

I am working on a coporate computer and it said it was unable to open that file type in protected view

If that is the only problem, then see if this helps, especially from post #3 on: XL2BB Icons greyed out

Or, I can email you a sample of the spreadsheet

That is not allowed in this forum - see #4 of the Forum Rules

 

[excelzrf]

If that is the only problem, then see if this helps, especially from post #3 on: XL2BB Icons greyed out


That is not allowed in this forum - see #4 of the Forum Rules

Here is the range I am taking about

Looking to have "Units Sold" fill in when there have been units completed (row 64). So, once units are completed, wait 3 months and sell 3 units. The formula is close but it is saying that units are sold when no units are completed yet, which is not possible. Please let me know if you have any questions, and thank you for your help!

Note: I also cannot have more unit sales than units exist. There are only 24 units to sell, so I cannot have a number of sales over 24.

Wayfarer Model V6 12.5.23.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
64	Units Completed					-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	12	-	12	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
65	Units Sold			-------------->	ALL Hardcode	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00
Monthly Buildout

Cell Formulas
Range	Formula
G64:N64	G64	=IF(COUNT(#REF!)>1,IFERROR(INDEX($G$6:$BN$6,1,G231),0),IFERROR(INDEX($G$6:$BN$6,1,G228),0))
O64:T64	O64	=IF(COUNT(A$6:$G6)>1,IFERROR(INDEX($G$6:$BN$6,1,O231),0),IFERROR(INDEX($G$6:$BN$6,1,O228),0))
U64:AJ64	U64	=IF(COUNT($G$6:G6)>1,IFERROR(INDEX($G$6:$BN$6,1,U231),0),IFERROR(INDEX($G$6:$BN$6,1,U228),0))
AK64:BN64	AK64	=IF(COUNT($G$6:AL6)>1,IFERROR(INDEX($G$6:$BN$6,1,AK231),0),IFERROR(INDEX($G$6:$BN$6,1,AK228),0))
G65:BN65	G65	=Assumptions!$E$6*(MOD(COLUMNS($G$64:G64),Assumptions!$E$7)=0)
B65	B65	='https://aro365139694-my.sharepoint.com/personal/zfanch_devilsthumbranch_com/Documents/Documents/Marker Hill Construction/Trails Townhomes/[Roam Townhomes VHardcodes - Sales Assumptions - Copy.xlsx]Income STMT'!B6

 

[Peter_SSs]

Good to see you have XL2BB up and running.

Does your mini sheet include this?

Can you make up a small example of the sheet(s) involved, manually fill in the results you want and post all that with XL2BB?

 

[excelzrf]

Please see the purple characters (hardcoded). This is what I want the formula to produce. Important part is that the "units sold" does not exceed 24 total.
Thank you!

Wayfarer Model V6 12.5.23.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
83	Units Completed																																	12		12
84	Units Sold					-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	3.00	-	-	-	-	-	-	-
85	Total Units Sold					24.00
Monthly Buildout

Cell Formulas
Range	Formula
G85	G85	=SUM(G84:BG84)

 

[Dave Patton]

T202312a.xlsm
	A	B					G																												AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG
84		Units Sold					0																												0	0	0	3	0	0	3	0	0	3	0	0	3	0	0	3	0	0	3	0	0	6	0	0	3
85		Total Units Sold (max 24)					24
6k

Cell Formulas
Range	Formula
G85	G85	=MIN(24,SUM(G84:BG84))

 

[excelzrf]

T202312a.xlsm
	A	B					G																												AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG
84		Units Sold					0																												0	0	0	3	0	0	3	0	0	3	0	0	3	0	0	3	0	0	3	0	0	6	0	0	3
85		Total Units Sold (max 24)					24
6k

Cell Formulas
Range	Formula
G85	G85	=MIN(24,SUM(G84:BG84))

I am looking for the formula to reflect my mini-sheet, which I hardcoded. Thank you for your reply but that does not match

 

[Dave Patton]

Please see your post #14

 

[Peter_SSs]

One example is not much to go on and it is not looking like a simple solution. What would be the expected results for this scenario, assuming that 'Assumptions' E6 and E7 are both 3?

excelzrf.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
64	Units Completed									4											5													12		12
65	Units Sold
Monthly Buildout

 

[excelzrf]

The expected scenario would be that the units start selling after they are completed. What else can I provide to help out?

 

[excelzrf]

Please see your post #14

I am looking for the sum of Column 84 to equal 24 units, spread out over 3 month increments in 3 unit increments.