Hi, I am in the real estate business and need to create a formula based on the following logic/scenario:

We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase.

My questions have two parts:

1. Is this something array formula can handle in one formula?

2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps.

For example,

Month 1-15 Rent $20

16-27 20.50

28-39 21

40-51 21.5

52-63 22

64-75 23

......

......

......

232-243 30

Please note that both and month and rent are not fixed but logical values.

I guess this would be a typical multiple IFs if not for its limitation (I need 20 bumps which would be 20 arguments). I was told I may be able to do this with Name Formula or Concatenate. And I have tried the Concatenate which looks like would be a MONSTER formula with 20 OR statements in it.

Is there a way the array formula can be applied here as well? I have searched this website and found a thread containing a similar situation where a LOOKUP was used beatifully. The difference is my ranges and results are all logical values instead of fixed values.

I am looking for answers to both of my questions. If anyone can shred any light on it, that would be great. I attached the sample here. Ask if you are not clear.

Thanks a million!!!!

We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase.

My questions have two parts:

1. Is this something array formula can handle in one formula?

2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps.

For example,

Month 1-15 Rent $20

16-27 20.50

28-39 21

40-51 21.5

52-63 22

64-75 23

......

......

......

232-243 30

Please note that both and month and rent are not fixed but logical values.

I guess this would be a typical multiple IFs if not for its limitation (I need 20 bumps which would be 20 arguments). I was told I may be able to do this with Name Formula or Concatenate. And I have tried the Concatenate which looks like would be a MONSTER formula with 20 OR statements in it.

Is there a way the array formula can be applied here as well? I have searched this website and found a thread containing a similar situation where a LOOKUP was used beatifully. The difference is my ranges and results are all logical values instead of fixed values.

I am looking for answers to both of my questions. If anyone can shred any light on it, that would be great. I attached the sample here. Ask if you are not clear.

Thanks a million!!!!

## Comment