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?

 

[Scott Huish]

=MOD(ROWS($A$1:A1)-1,2)*2

Format these cells as 0 "sales"

Copy down.

Book1
	A	B
1	1/1/2023	0 sales
2	2/1/2023	2 sales
3	3/1/2023	0 sales
4	4/1/2023	2 sales
5	5/1/2023	0 sales
6	6/1/2023	2 sales
7	7/1/2023	0 sales
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=MOD(ROWS($A$1:A1)-1,2)*2

 

[Peter_SSs]

Welcome to the MrExcel board!

If you want to vary the amount and/or frequency, perhaps this slight modification?

23 11 30.xlsm
	A	B
1	How many?	3
2	How often?	4
3
4	Jan-23	0
5	Feb-23	0
6	Mar-23	0
7	Apr-23	3
8	May-23	0
9	Jun-23	0
10	Jul-23	0
11	Aug-23	3
12	Sep-23	0
13	Oct-23	0
14	Nov-23	0
15	Dec-23	3
Sales

Cell Formulas
Range	Formula
B4:B15	B4	=B$1*(MOD(ROWS(B$4:B4),B$2)=0)

 

[excelzrf]

That worked great! Thank you. How did you know this?

 

[excelzrf]

Actually, I modified it to be =Assumptions!$E$6*(MOD(COLUMNS($G$64:I64),Assumptions!$E$7)=0)

But I need it to only calculate if the cell above it is >0. I tried an if statement and it is only returning 0's. If i do not have that, it is returning the number of unit sales even though the number of finished units is still 0. I need it to only return when number of units finished is >0.

See photo attached please

 

[Peter_SSs]

That worked great!

Which one (or both)? There have been two suggestions.

How did you know this?

Who are you asking? Two people have responded.

I modified it to be =Assumptions!$E$6*(MOD(COLUMNS($G$64:I64),Assumptions!$E$7)=0)

What is the name of the worksheet that this formula is actually on?

See photo attached please

Unfortunately, we cannot copy from an image for testing, nor can we see any formulas that are used. 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?
Explain again in relation to the specific sample data that you post.

 

[excelzrf]

I meant to reply to you, Peter. The worksheet I am on is called "Monthly Buildout" and the Assumptions tab is where I am pulling the unit sales assumptions.

 

[Peter_SSs]

OK, thanks for the clarifications. What about the last section of my previous post?

 

[excelzrf]

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

 

[excelzrf]

I will explain it as well as I can without a spreadsheet. Or, I can email you a sample of the spreadsheet so you can see the issue if that works?