financial model- creating assumptions

[politicalangel]

Hi,
Does anyone know a way I can change the current formula to make it more of a "variable".
What I mean.. Am working on a new store model, and I am assuming that new stores will be opened on specific months.. as we forecast out this months can change.. I want my formula to
1) change the amount of stores opening in a month the IF function is able to change (am assuming I need the AND function to be able to change corresponding column B)

I want to be able it to be driven by what is inputted in row 4

Please advice

 

[Luke M]

Formula in D11, copied down and to the right:
=IF(AND($B11<=D$9,COUNTIF($B$11:$B11,"<="&D$9)<=SUM($D$4:D$4)),($C$11/12),0)

Formula checks col B to make sure store is allowed to be open in this month, and checks row 4 to see how many stores total can be opened. So, if 3 stores are allowed to open in June based on col B, but only 2 stores are allowed to open in Jun and 1 in July, then in your formula table, the 3rd store won't show a number till July.

 

[politicalangel]

Formula in D11, copied down and to the right:
=IF(AND($B11<=D$9,COUNTIF($B$11:$B11,"<="&D$9)<=SUM($D$4:D$4)),($C$11/12),0)

Formula checks col B to make sure store is allowed to be open in this month, and checks row 4 to see how many stores total can be opened. So, if 3 stores are allowed to open in June based on col B, but only 2 stores are allowed to open in Jun and 1 in July, then in your formula table, the 3rd store won't show a number till July.[


Thanks... it worked great... but is there a way values in Colume B can update based on where row 4 has a value?

So for instance if I changed opening stores on row 4.. I want it to change column B to the corresponding Month?


Thanks!

 

[Luke M]

Ah, misunderstood your question. Change the formula in D11 to:
=IF(ROWS(D$11:D11)<=SUM($D$4:D$4),($C$11/12),0)

And formula in B11:B15 becomes:
=INDEX($D$9:$N$9,MATCH(1,D11:N11)+1)

 

[politicalangel]

worked perfectly... Thank you