Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Calculate Increase Based On Criteria

  1. #1
    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!!!!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Calculate Increase Based On Criteria

    Hi,

    Not sure about this..

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

    where C23 houses the Rent Bump Month

  3. #3
    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. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Calculate Increase Based On Criteria

    Hi,

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

  5. #5
    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. #6
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculate Rank Based on Criteria
    By NathanHindmarsh in forum Excel General
    Replies: 21
    Last Post: October 27th, 2011, 11:44
  2. Calculate Percent Increase Based On Matrix
    By jag.seven in forum Excel General
    Replies: 6
    Last Post: January 16th, 2008, 10:33
  3. Calculate Criteria Based Averages
    By pepemum22 in forum Excel General
    Replies: 1
    Last Post: January 16th, 2008, 04:17
  4. Calculate Percentage Based On Criteria
    By AFfireman in forum Excel General
    Replies: 4
    Last Post: December 30th, 2006, 03:30
  5. Calculate Percentage Based on Criteria
    By pytelium in forum Excel General
    Replies: 13
    Last Post: August 14th, 2006, 08:55

Bookmarks

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