Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Calculate Increase Based On Criteria

1. I agreed to these rules
Join Date
17th January 2009
Posts
13

## Calculate Increase Based On Criteria

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!!!!

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Calculate Increase Based On Criteria

Hi,

=INDEX(D19:X19,MATCH(TRUE,INDEX(D18:X18>=C23,0,0),0))

where C23 houses the Rent Bump Month

3. I agreed to these rules
Join Date
17th January 2009
Posts
13

## Re: Calculate Increase Based On Criteria

Hi, Krish. Thanks for the try but it does not work.

What I need is to be able to determine the rent of any month between month 1 and month 240 based on the rent bump date and rent below. For example, say it is month 54, the rent should be \$22 based on the criteria.

The row I need to fill in with the array formula is from D15- II15, which is the yellow highlighted cells.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Calculate Increase Based On Criteria

Hi,

=LOOKUP(C23,D18:X18,D19:X19)

5. I agreed to these rules
Join Date
17th January 2009
Posts
13

## Re: Calculate Increase Based On Criteria

C23 is blank in my spreadsheet, there is no value......

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
17th January 2009
Posts
13

## Re: Calculate Increase Based On Criteria

Got it!

Should be LOOKUP(D6,\$D\$18:\$X18\$18,\$D\$19:\$X\$19) and copy across!

Now it becomes clearer to me what it does. Thanks a lot, Krish.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno