If statement Problem

[excobra]

Guys ,


I have a chain of 4 hotels say from b7 to b10. Each hotel will take certain no of years to construct. once construction is finished it will start generating revenue from next year.




Table 1 shows the construction period where 1= construction and 0=no construction.


Table 2 This table should be automatically updated once construction finished and it should start earning revenue i.e. 1= revenue and 0= no revenue


Table 3 This is what i am getting using if statement which is wrong. Revenue should continue instead of just one year as shown in table 3


Please advice where is problem in my formula. End result should look like table 2

[FONT=source_sans_proregular]You can sdownload the file from here https://mega.nz/#!s58GwRpI!dJnT4kTq2Iop9lS-xDM-g7bgtZz5YSTz1KbtsPehXiY



[/FONT]

 

[oldbrewer]

I never download so....

hotel1

hotel2

hotel3

hotel4

hotel5

hotel6

hotel7

hotel8

hotel9

hotel10

01/01/2015

start

01/07/2015

start

start

01/01/2016

finish

start

start

01/07/2016

finish

finish

start

01/01/2017

start

finish

finish

01/07/2017

start

finish

01/01/2018

finish

start

01/07/2018

finish

start

01/01/2019

finish

01/07/2019

finish

hotel

hotel

hotel

hotel

hotel

hotel

hotel

hotel

hotel

hotel

1

2

3

4

5

6

7

8

9

10

01/01/2015

0

0

0

0

0

0

0

0

0

0

01/07/2015

0

0

0

0

0

0

0

0

0

0

01/01/2016

0

R

0

0

0

0

0

0

0

0

01/07/2016

0

R

0

0

R

0

0

0

R

0

01/01/2017

0

R

0

0

R

R

0

R

R

0

01/07/2017

0

R

0

0

R

R

0

R

R

R

01/01/2018

0

R

0

R

R

R

0

R

R

R

01/07/2018

R

R

0

R

R

R

0

R

R

R

01/01/2019

R

R

0

R

R

R

R

R

R

R

01/07/2019

R

R

R

R

R

R

R

R

R

R

01/01/2020

R

R

R

R

R

R

R

R

R

R

01/07/2020

R

R

R

R

R

R

R

R

R

R

01/01/2021

R

R

R

R

R

R

R

R

R

R

01/07/2021

R

R

R

R

R

R

R

R

R

R

01/01/2022

R

R

R

R

R

R

R

R

R

R

01/07/2022

R

R

R

R

R

R

R

R

R

R

is this broadly what you want to see ?

<colgroup><col><col><col span="11"></colgroup><tbody>
</tbody>

 

[excobra]

Here you go i have copied the data

	Input Table
	Construction	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
	Hotel 1		1	1	1	-	-	-	-	-	-	-
	Hotel 2		-	1	1	-	-	-	-	-	-	-
	Hotel 3		-	-	1	1	-	-	-	-	-	-
	Hotel 4		-	-	-	1	1
	Output Table
	Revenue		Desired table: This is what the actual result should look like
	Hotel 1		-	-	-	1	1	1	1	1	1	1
	Hotel 2		-	-	-	1	1	1	1	1	1	1
	Hotel 3		-	-	-	-	1	1	1	1	1	1
	Hotel 4		-	-	-	-	-	1	1	1	1	1
	Output Table
	Revenue		What I am actually getting using this formula
			=IF(D7=1,0,IF(C7=0,0,1))
	Hotel 1		-	-	-	1	-	-	-	-	-	-
	Hotel 2		-	-	-	1	-	-	-	-	-	-
	Hotel 3		-	-	-	-	1	-	-	-	-	-
	Hotel 4		-	-	-	-	-	1	-	-	-	-

<colgroup><col><col span="12"><col></colgroup><tbody>
</tbody>

 

[AliGW]

In D24 copied acroos and down:

=IF(COUNTIF(D7:$M7,1)>0,0,1)

Cross-posted here: If statement Problem

 

[oldbrewer]

Construction	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Hotel 1		1	1	1
Hotel 2			1	1
Hotel 3				1	1
Hotel 4					1	1
Revenue	Desired table: This is what the actual result should look like
Hotel 1	-	-	-	1	1	1	1	1	1	1
Hotel 2	-	-	-	1	1	1	1	1	1	1
Hotel 3	-	-	-	-	1	1	1	1	1	1
Hotel 4	-	-	-	-	-	1	1	1	1	1
Output Table
Revenue		What I am actually getting using this formula
Hotel 1		-	-	-	1	-	-	-	-	-	-
Hotel 2		-	-	-	1	-	-	-	-	-	-
Hotel 3		-	-	-	-	1	-	-	-	-	-
Hotel 4		-	-	-	-	-	1	-	-	-	-
	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Hotel 1					1	1	1	1	1	1		row 28
Hotel 2					1	1	1	1	1	1
Hotel 3						1	1	1	1	1
Hotel 4							1	1	1	1
		formula in cell B28 (hotel 1 year 1 )
		=IF(LEFT(A$1,1)="Y",IF(B2="",IF(SUM(A2:$B2)>1,1,""),""),"")

<colgroup><col><col span="14"></colgroup><tbody>
</tbody>

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.