Operators in Array Formulas

[bmccabe362]

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-comfficeffice" /><o></o>
{=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></o>
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></o>
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></o>
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></o>
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></o>
{=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></o>
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></o>
Thank you very much for your help and, by the way, I don’t know vba .

 

[shg]

Advanced Filter: http://www.contextures.com/xladvfilter01.html

 

[scottylad2]

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]

Oops. 2003. Thanks for asking...

Brian

 

[bmccabe362]

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]

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]

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.