Rent Roll Help!

[elmsley4]

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

 

[barry houdini]

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

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

 

[elmsley4]

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)???

 

[barry houdini]

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

 

[Yogi Anand]

Hi elmsley4:

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

Book1
	A	B	C	D	E	F	G	H	I
1	Tenant	LeaseStart	LeaseEnd	SFT	Rent				database
2	T_3	6/15/2007	6/14/2008	1200	1500		FALSE		criterion
3	T_4	3/1/2008	2/28/2011	2000	2500				DataTable
4	T_5	7/9/2008	7/8/2009	1200	1500		DataTable Solution
5	T_6	11/16/2008	2/28/2010	2000	2500		LeaseExpireYear	SFT	Rent
6	T_7	3/26/2009	7/8/2012	1200	1500		2008	1200	1500
7	T_8	8/3/2009	2/28/2013	2000	2500		2009	1200	1500
8	T_9	12/11/2009	7/8/2012	1200	1500		2010	2000	2500
9	T_10	4/20/2010	2/28/2015	2000	2500		2011	2000	2500
10	T_11	8/28/2010	7/8/2014	1200	1500		2012	2400	3000
11	T_12	1/5/2011	2/28/2015	2000	2500		2013	2000	2500
12							2014	1200	1500
13							2015	4000	5000
Sheet3

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

 

[elmsley4]

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

 

[elmsley4]

Quickly,

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

This isn't a text box....

 

[Yogi Anand]

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.

 

[cdh121]

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.

 

[Yogi Anand]

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