Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Calculate Increase Based On Criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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
    Kris

    ExcelFox

    Comment


    • #3
      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.

      Comment


      • #4
        Re: Calculate Increase Based On Criteria

        Hi,

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

        ExcelFox

        Comment


        • #5
          Re: Calculate Increase Based On Criteria

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

          Comment


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

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X