Property Management Availability Calculator

[sg101]

Hello - I am working on a Property Management spreadsheet to track the availability of vacant units (and upcoming vacant units). I'm trying to write a formula to count the number of current units that are listed as vacant, so that I can ultimately report on the overall unit availability as of any given period (today, 15, 30, 60, 90-days out). All of the data in this spreadsheet is dynamic and each field can change at any time. There are also a lot of blank fields as well. I am using Excel 2003.

Here are the fields/data that I am using for my analysis:
Column A - (Reporting Period Dates): A2 = 5/20/08, A3 = 6/5/08, A4 = 6/20/08, etc.
Column B - (Unit Status: V=Vacant, O=Occupied): B2 = V, B3 = V, B4 = V, B5 = V, B6 = V, B7 = V, B8 = O, B9 = O, B10 = O, B11 = O, B12 = O, B13 = O
Column C - (M/O Date. These dates represent when the current resident will move out) - Cells C2:C13 contain dates for when each tenant will move out
Column D - (M/I Date. These dates represent when the future resident will move in) - Cells D2:D13 contain dates when the future tenant will move in

If my reporting period is 5/20/08 (cell A2), then I need to calculate how many Vacant units (B2:B13) I will have as of 5/20. The formula will need to count all of the current Vacant units (B2:B13), plus it will also have to take into account any Move Outs (C2:C13) during the reporting period. For example, let's say we have an Occupied unit that is scheduled to move out on 5/15/08. The cell will show this as "O" but the formula will need to determine if the M/O date is <= the Reporting Date of 5/20/08 (A2). If so, then Excel would count this unit as a Vacant for the period.

In addition to this, I also need the formula to look at upcoming Move IN dates (D2:D13). The formula will also have to determine if the MI date is <= the Reporting date (A2). If the MI date falls prior to the Reporting Date, then this unit should be considered as Occupied and should NOT be added to the total available units. NOTE - there are quite a few cells in column D that are blank and don't have MI dates. If there isn't a MI date, then the formula should assume that those units are Vacant if it is past the prior tenants MO date.
I'm assuming that Excel can handle multiple conditions like this, however, I am not smart enough to figure it out. I would appreciate any help any of you can provide. Hopefully this isn't too confusing.

Thanks!
Steve

[WinteE]

I think it would help a lot when you attach a (sample) file to your post.

[sg101]

Thanks. I didn't know I could do that. I just posted a sample file with the data set I described.

[Bjornar]

Hello and welcome to the forum.
I'm not sure this is how you want your layout, but I have made an example for you that I think will give you some ideas about what is possible, and how you can do it.

[sg101]

Thanks a million Bjornar!! This is exactly what I was looking for and should work great! I really appreciate your help.

One quick question - is there a way for me to copy the formulas you created accross all of the rows so that I don't have to change the cell number for each row? The spreadsheet I attached was just a test file. My actual file has 395 rows, so I need a quick way to apply these formulas accross the board. Do you know what I mean?

Thanks again!

Steve

[Bjornar]

I'm glad you could use my solution.
The formula could be copied to as many rows and columns as you like, I have used a combination of absolute and relative references (that is the $ symbols in the cell references), so the formula should change itself when you copy it.

[sg101]

Great, thanks a lot.

Quick final question. What is the difference between a value of $A1 and $A$1? What does the second $ do to the formula?

[sg101]

Disregard the last comment. I just figured it out. Thanks again for your help. I REALLY apprecite it!!!

Steve

[sg101]

Sorry, I have one more quick question. My master spreadsheet currently only contains 85 rows of actual data. I applied your formulas to my sheet and everything worked perfect. The only problem is that if I apply these formulas to any additional blank rows, it is calculating all of the blank rows as "O's".

Is there a way for me to be able to apply these formulas and have them NOT return a value if the row/cell is blank? I know I can manually just copy the formulas down for additional rows, but I am hoping to keep it all automated and don't want my users to have to worry about that if possible.

Thanks!

[Bjornar]

I have now changed the conditional formatting of the cells, and change the formula so you can put it in blank rows. If you fill in a O or V in one of the blank cells in column B you will see that the rest of that row will be formatted and filled with O or V.