bmccabe362

New Member
Joined
Jan 4, 2010
Messages
11
By way of explanation, I am in the real estate business. Over the years I have found the MLS to be too one-dimensional, so I built an ever-evolving means of illuminating the ways in which markets behave. For example, consider the following array formula:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
{=SUM(IF(PENDING_DATES>=BEGINNING_DATE,IF(PENDING_DATES<=END_DATE,IF(SALE_PRICES>=BEGINNING_PRICE,IF(SALE_PRICES<END_PRICE,IF(OWNERSHIP_TYPES=OWNERSHIP,1,0),0)))))}
<o:p></o:p>
This formula would return the number of houses that went under contract between two dates, in a particular price range, and having a particular ownership type. All the check-values in italics are pulled from either the same or other worksheets. Now to my problem:
<o:p></o:p>
I want to add a new variable to the existing formula…bedrooms. So, given the specified pending dates, price range and ownership type, I want to know how many 4-bedroom homes sold, or how many 3- and 4-bedroom homes sold, etc. Furthermore, since I won’t always be using the bedrooms crosstab, the default bedroom value has to include all houses that sold that had bedrooms, which would be the same as the returned value before the introduction of the bedroom variable.
<o:p></o:p>
Were I using the value of the bedroom variable only once I would add it by hand. However, many different formulas need to access it so the only practical way to do it seems to be an easily customized pull-down list. The reason is that in one market I may be interested in 2 and 3 bedroom houses, while in another it might be homes with 3 or more bedrooms (>=3). Also, the default bedroom value has to include all houses that sold (any house with a bedroom).
<o:p></o:p>
So, here is where I hit the wall. In order to get my list of bedroom check values to work I have to include operators that will enable me to account for all kinds of possibilities. The following is where I am and it doesn’t work (as you know).
<o:p></o:p>
{=SUM(IF(PENDING_DATES>=BEGINNING_DATE,IF(PENDING_DATES<=END_DATE,IF(SALE_PRICES>=BEGINNING_PRICE,IF(SALE_PRICES<END_PRICE,IF(OWNERSHIP_TYPES=OWNERSHIP, IF(NUMBER_BEDROOMS=$AY$11,0),0))))))}
<o:p></o:p>
In the above, =$AY$11 is the cell reference to the bedroom list. Since my check values sometimes contain operators, the values don’t work with the “=” in the expression, IF(NUMBER_BEDROOMS=$AY$11.
<o:p></o:p>
Thank you very much for your help and, by the way, I don’t know vba :confused:.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
what version of excel are you using? 2007 and later will allow you to use the sumifs which allows multiple criteria, and you wouldn't need the= in your last argument
 

bmccabe362

New Member
Joined
Jan 4, 2010
Messages
11

ADVERTISEMENT

Thank you for your response, shg4421******** type=text/javascript> vbmenu_register("postmenu_2559171", true); *********> . An advanced filter would work if the data was to be used in a limited way. That's not the case here. Active listings, sold listing and even expired listings are cross-tabbed with the bedroom data.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,798
Office Version
  1. 2010
Platform
  1. Windows
If all the data were in the same table, it wouldn't matter, would it? Active/Sold/Expired is just one more field, is it not?
 

bmccabe362

New Member
Joined
Jan 4, 2010
Messages
11
It's not one field. The best way for me to explain it is to refer you to my web site at www.wessex-home.com. While it won't show you specifically what I am referring to, I think it will give you a good idea of how the data comes together. Underlying the system is this mechanism: I download the raw data that comprises hundreds of MLS records that span a minimum of 4 years. There is data for sold homes, active listings and expired listings. Once the data is imported, most of the analysis takes place automatically. What isn't automatic are various settings such as ownership type, date ranges, price ranges and, hopefully, the number of bedrooms. Such settings affect all the data at one time. For example, if I change the ownership type from fee simple to condo, all the charts, tables and analysis write up information changes to reflect the condo market in a particular town. One last thing: a great deal of analysis, which takes place without my intervention, combines sold, active and expired listings in order to illuminate trends, etc. That's why is important that settings changes impact all the data at the same time. :)
 

Forum statistics

Threads
1,137,349
Messages
5,680,963
Members
419,946
Latest member
Trickay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top