Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Rent Roll Help!

This is a discussion on Rent Roll Help! within the Excel Questions forums, part of the Question Forums category; I feel like an idiot b/c I think this shouldn't be too hard: I have a table for an office ...

  1. #1
    New Member
    Join Date
    Aug 2006
    Posts
    24

    Default Rent Roll Help!

    I feel like an idiot b/c I think this shouldn't be too hard:

    I have a table for an office building showing tenants and lease expirations.
    I want to show, as a percentage, the amount of tenants leaving in any given year.

    Layout:
    Column A = Tenant Name
    Column B = Lease Start*
    Column C = LEASE END*
    Column D = SQUARE FEET
    Column E = BASE RENT

    *Dates are entered as follows: 1/15/2005

    I can do this with a funky SUMIF feature (for Sq.Ft and Base Rent calculations), but isn't there something easier?

    I want to know:
    1. TOTAL Sq.Ft expiring for years 2008 - 2015 and,
    2. TOTAL Base Rent expiring for years 2008 - 2015.

    Everything else will be easy to calculate. Is there a SUMIF command that will display all leases expiring ONLY in 2009, 2010, 2011, etc...???

    Thanks for any help!

    -JRG
    Last edited by elmsley4; May 7th, 2008 at 06:25 PM.

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,505

    Default Re: Rent Roll Help!

    Try using SUMPRODUCT, e.g. for total base rent expiring for 2008-2015

    =SUMPRODUCT((YEAR(C2:C100)>=2008)*(YEAR(C2:C100)<=2015),E2:E100)

  3. #3
    New Member
    Join Date
    Aug 2006
    Posts
    24

    Default Clarification on post

    Sorry I wasn't very clear.

    I need to know ONLY the amount of SF expiring in 2008, and in the next cell, ONLY amount of SF expiring in 2009, then ONLY the amount of SF expiring in 2010, etc...

    I don't need to know ALL the leases expiring from 2008 - 2015 as a lump sum, I would just sort the data by leases expiring and add the appropriate cells.

    Thanks for the help!!!

    -JRG

    Is there a CSE formual I need to use in order to have the formula return the total
    SF expiring >2008 but <2009?

    Also, do I need to convert the format of dates expiring to just a YEAR format? (e.g. 1/15/2008 needs to be converted to 2008)???

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,505

    Default Re: Rent Roll Help!

    If you use SUMPRODUCT it's just a regular formula, you don't need CSE....and you don't need to convert the dates.

    List the years in, say, G2 down then use this formula in H2 copied down for a sum of rent for leases expiring each year

    =SUMPRODUCT(--(YEAR(C$2:C$100)=G2),E$2:E$100)

    change ranges as necessary

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Rent Roll Help!

    Hi elmsley4:

    Following is a solution using EXCEL's one-variable DataTable ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    TenantLeaseStartLeaseEndSFTRent database
    2
    T_36/15/20076/14/200812001500 FALSE criterion
    3
    T_43/1/20082/28/201120002500 DataTable
    4
    T_57/9/20087/8/200912001500 DataTable Solution
    5
    T_611/16/20082/28/201020002500 LeaseExpireYearSFTRent
    6
    T_73/26/20097/8/201212001500 200812001500
    7
    T_88/3/20092/28/201320002500 200912001500
    8
    T_912/11/20097/8/201212001500 201020002500
    9
    T_104/20/20102/28/201520002500 201120002500
    10
    T_118/28/20107/8/201412001500 201224003000
    11
    T_121/5/20112/28/201520002500 201320002500
    12
    201412001500
    13
    201540005000
    Sheet3

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    computed criterion formula in cell G2 is ... =YEAR(C2)=$G$3

    formula in cell H5 is ... =DSUM($A:$E,4,$G$1:$G$2)

    formula in cell I5 is ... =DSUM($A:$E,5,$G$1:$G$2)

    DataTable Range is ... G5:I13; column_input cell is $G$3
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    New Member
    Join Date
    Aug 2006
    Posts
    24

    Default Re: Rent Roll Help!

    This is awesome. I hate to say it, but JUST when I feel I'm getting pretty good w/ Excel, somoene like you makes me feel like a kid! (which is a good thing).

    Thanks for the help. I don't fully understand how/why the above formulas work together, but I can appreciate the efficiency of the model.

    Thank you again!

    Best,

    Joel

  7. #7
    New Member
    Join Date
    Aug 2006
    Posts
    24

    Default Re: Rent Roll Help!

    Quickly,

    How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

    This isn't a text box....

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Rent Roll Help!

    Quote Originally Posted by elmsley4 View Post
    Quickly,

    How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

    This isn't a text box....
    Hi elmsley4:

    H5 and I5 indeed house the DSUM formulas that are needed for the one-variable DataTable. And no there are no TEXT Boxes, cells H5, and I5 are CustomNumber Formatted to display "SFT", and "Rent" (to optionally delineate more meaningfully the headers for the respective columns of the subsequent rows of the DataTable.)

    Please look into the use of EXCEL DataTable to fully understand the entire setup for this proposed solution.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    New Member
    Join Date
    Jul 2005
    Location
    Grand Rapids, MI
    Posts
    25

    Default Re: Rent Roll Help!

    I am going to ask sort of the same question, for the same type of spreadsheet: I have a sheet containing the same basic data (square footages in Column C, Expiration Dates in Column E). But, I want to be able to see how much square footage is expiring within 6 months of TODAY, 12 months of TODAY, etc., with the total SF for each date range summing into one cell. Bonus points for conditional formatting that will highlight the names of tenants (in Column B) whose lease are expiring in those ranges.

    Thanks!

    C.

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Rent Roll Help!

    Quote Originally Posted by elmsley4 View Post
    Quickly,

    How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

    This isn't a text box....
    Hi elmsley4:

    Today I was prompted for solution to a followup question after a long time since 2008 vintage of this thread for which I Used Google spreadsheet's QUERY function ... and I thought I will revisit this question using the QUERY function ... and I have presented that solution in my following blog post

    yogi_Compute Year By Year SquareFootage And Rent For LeaseEnd Year
    Cloud Computing -- Google Docs Way: yogi_Compute Year By Year SquareFootage And Rent For LeaseEnd Year

    so please check it out to see how this works for you.


    Make It A Great One
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com