Counting Number of Vacant Units

**NoLeafClover** · 12-09-2015, 10:19 PM

I am creating an analysis spreadsheet that lists a shopping centers tenants, their square footage, and their pro-rate percentage share of space that their unit occupies in the entire shopping center.

I want to add another column that shows the number of vacancies by month. Each month there is a percentage (i.e. 100% for January if January is fully occupied) showing the occupancy. I would like to extract a count of the number of vacancies that occur if the occupancy number matches up to the difference of the percentage share of each tenant.

For example, I have three tenants.

Tenant #1: 2,400SqFt 34.4%

Tenant #2: 2,567SqFt 36.8%

Tenant #3: 2,000SqFt 28.7%

Total: 6,967SqFt 100%

I have made multiple if and count statements but without writing up every single possible outcome that a months occupancy percentage would equal less than 100% have can determine the number of blank units, it would take house of formulating each possible outcome.

Is there a formula statement that can calculate all possible scenarios to match the vacancy % and produce a result that would state 2 vacant units or 1 vacant unit...?

I'm spinning my wheels in my office here and figured I would defer my question to someone more advanced than me on this forum.

Anyones help would be greatly appreciated.

**FlameRetired** · 12-09-2015, 11:06 PM

NoLeafClover,

Welcome.

Without a sample Excel file it is difficult to say. Please upload a workbook that exemplifies what you envision. Include a before section and an after section with desired results typed in if necessary.

Please be sure to desensitize the data.

Also be sure to upload an actual Excel workbook. Screenshots and pics are not usually helpful. This way we can work directly with what you are looking at.

The instructions for uploading are in the FAQs. In the meantime here is a direct link to that section.
http://www.excelforum.com/faq.php?fa...b3_attachments

**NoLeafClover** · 12-10-2015, 01:19 PM

Thank you.

I have attached the excel I am building. The second tab is where I highlighted the # of vacancies column to count the number of vacancies based upon the corresponding occupancy % column.

**FlameRetired** · 12-10-2015, 03:43 PM

In the attached I put this formula in Sheet2 E36:E47. It returns the percentages directly from Sheet1

Q39:Q50. It saves having to jump across sheets / ranges to find / verify the percentages as you can see

the percentages in the next column to the right. It seemed easier.

Formula:

=INDEX(Sheet1!$P$39:$Q$50,MATCH($A36,Sheet1!$P$39:$P$50,0),2)

If I interpret your intent from your existing formulas correctly the count of vacancies depends solely

upon the number of blank cells in Sheet1 B10:B12. With that understanding I put this formula in

D36:D47.

Formula:

=IF($E36=100%,0,COUNTIF(Sheet1!$B$10:$B$12,""))

I then deleted some Tenant cells data in B10:B12 to test it.

Does this help?

**NoLeafClover** · 12-10-2015, 04:04 PM

I should elaborate the reasoning why.

If I count the number of vacancies to the months occupancy %, then it will show the corresponding number of vacancies for that month. If i were to calculate it based upon the tenant name, it would not return for that month, the number of vacancies. Instead it would return a figure that could be applied to any month in time as the tenant name fields is only for a current tenant base which is variable. the tenant square footage base will always be fixed and their pro-rate % share of space is fixed always as well.

**NoLeafClover** · 12-10-2015, 03:59 PM

I apologize FlameRetired as I should of mentioned that those formulas int here were just different tests I was running to make it work.

I am hoping to have a formala than can count the number of vacancies based upon the variations of percentages in vacancy.

For example, if it see's 65.6% occupied, it will put 1 for vacancy because it knows suite #1 is vacant. if it see's 28.7% occupied, it will return 2 as it would know then that suite #1 and 3@ are vacant. Is there a formula to calculated the multiple results and return the number of vacant?

**FlameRetired** · 12-10-2015, 04:21 PM

NoLeafClover, thank you. That's clearer. If this is doable he Total Vacant (SF) figures will need to be something that can reflect a distinct combination.

In other words is each unit's square footage unique? If there are say multiple 2,400 sqft units and one or more 4,800 sqft units et al then

I am not sure there is a method that can discriminate if vacant square footage is one vacancy, two ...... etc.

Edit I suppose the better question I should ask is how are the Total Vacant (SF) figures derived? Perhaps your solution is there.

**NoLeafClover** · 12-10-2015, 04:42 PM

Is it possible to formulate a range? So let's say, we know each tenant is roughly 30% of the total leasable area. If there is a range saying between 20%-40% occupancy, then equal 2 for vacancy (because two tenants being vacant should equate to about 2/3's of the total %).

Am I making sense and is this possible?

**FlameRetired** · 12-10-2015, 04:45 PM

Originally Posted by NoLeafClover

Is it possible to formulate a range? So let's say, we know each tenant is roughly 30% of the total leasable area. If there is a range saying between 20%-40% occupancy, then equal 2 for vacancy (because two tenants being vacant should equate to about 2/3's of the total %).

Am I making sense and is this possible?

I don't know. I'll try that.

**FlameRetired** · 12-10-2015, 04:51 PM

Try this in column D Sheet2.

Formula:

=IF($E36=100%,0,LOOKUP($C36/Sheet1!$C$13,{0,1,2,3}/3,{3,2,1}))

This relies upon the Total Vacant (SF) figures corresponding to each month (given) in column C.

Edit No. That's not working. I'm confusing myself.

This.

Formula:

=IF($E36=100%,0,LOOKUP($C36/Sheet1!$C$13,{0,1,2,3}/3,{1,2,3}))

**FlameRetired** · 12-10-2015, 06:43 PM

NoLeafClover,

Would you be open to a lookup table? Something along these lines. The formula in column O simply counts the zeros

Formula:

=COUNTIF($H2:$J2,0)

that correspond with each combination expressed as vacancy or occupied.

Edited More usable lookup table. You can also work directly off the Percentage Occupied with this.

Row\Col	H	I	J	K	L	M	N	O
1	tenant 1	tenant 2	tenant 3		Occupied sqft	Vacant sqft	% Leased	# Vacant
2	0	0	0		0	6967	0%	3
3	0	0	2000		2000	4967	29%	2
4	2400	0	0		2400	4567	34%	2
5	0	2567	0		2567	4400	37%	2
6	2400	0	2000		4400	2567	63%	1
7	0	2567	2000		4567	2400	66%	1
8	2400	2567	0		4967	2000	71%	1
9	2400	2567	2000		6967	0	100%	0

**NoLeafClover** · 12-11-2015, 01:31 PM

Thanks FlameRetired!

It took a bit for me to understand it but I got it to work. I actually had to change the values to reflect decimals to return the property count but none the less I appreciate your help!

I wasn't able to figure out how the table would work. Do you mind uploading your excel so I can see how the table works?

**FlameRetired** · 12-11-2015, 02:28 PM

NoLeafClover,

I'm more than happy to do that.

Before I do an unpleasant thought occurred to me. How many occupancies will you be tracking?

With 3 there are 8 possible combinations requiring 8 rows. With say 5 it would be 32 ... with 20 it would require over a million rows.

Does this change anything?

**FlameRetired** · 12-11-2015, 04:49 PM

See the attached.

You can experiment with how it can be used with an INDEX / MATCH formula. I arbitrarily chose K12 in the attached. Test

different percentages in J12. The formula in K12 is

Formula:

=INDEX($O$2:$O$9,MATCH(J12,$N$2:$N$9,1))

**FlameRetired** · 12-13-2015, 05:21 PM

NoLeafClover,

I had a chance to work with this some more.

I applied an extended version of that lookup table in your upload. It seems more flexible.

I arbitrarily put it in A1:I9. I also sorted the table on “% Leased” descending. It seemed easier and more

intuitive to work with. That can be changed to suit. Just be sure to change the 3rd argument in MATCH

accordingly.

The formula in D36:D47 is

Formula:

=INDEX($H$2:$H$9,MATCH(E36,$G$2:$G$9,-1))

I tried forcing some figures in E40 to see how this performs. Let me know what you think.

**NoLeafClover** · 12-14-2015, 08:36 PM

Wow! You sir truly are a Genius!

I am going to analyze how this formula works so I can replicate it in different leasing models with different tenant space sizes.

One question, why did you use split screen on sheet 2?

P.S. Sorry for the late response, I was out of the office over the weekend.

Counting Number of Vacant Units

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Re: Counting Number of Vacant Units

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] formula for a different cost per number of units

Vacant Units - Multiple Entries but only showing on status for most recent entry

Number of Units and Employee required

[SOLVED] Calculate the Bonus units according to the quantity of the units bought (Help)

Excel 2007 : Calculating number of units without vlookup

Limit number of units and computers per customer

[SOLVED] How to determine the number of units?

Bookmarks

Bookmarks

Posting Permissions