How can I calculate average/min/max from an array of data?

**JohnnyOnTheSpot** · 07-01-2013, 08:32 PM

I'm tracking real estate sales, my data columns are like this:

date sold, address, square feet, sales price, $ per sq ft, garage (y/n), pool (y/n)

I want to be able to calculate average sales price or $ psf based on the other criteria ... for example, average sales price of houses with pools in time frame 1/1/13 - 2/1/13. Okay, so as long as my data array is static, I can use simple formulas (e.g., "average(e1:e50)"; "max(d15:d35)") which calculate values based on cells in the identified range. But as soon as I re-sort my data then the returns from those static formulas or no longer reliable (i.e., if I sort on price instead of date, then all records within my desired date range are no longer in the same cells as before).

Vlookup appears to be too simple for my purposes as it returns a value from a cell in a row based on the value of the first column within that row ... but I'm trying to calculate values (average, min, max) for ranges based on values in multiple other columns, not just the first column.

Anybody have any ideas?

thanks!

**alansidman** · 07-01-2013, 09:18 PM

Suggest you post a sample workbook for analysis.

**JohnnyOnTheSpot** · 07-01-2013, 09:41 PM

Here is the basic data set I'm working with

housing sales tracker.xlsx

**alansidman** · 07-01-2013, 10:17 PM

You should use the Sumifs function

Here is a quick tutorial on it

http://office.microsoft.com/en-us/ex...010342933.aspx

then because you have dates in your criteria look at this link also

http://www.pcreview.co.uk/forums/sum...-t3954866.html

Also click on the SumProduct Video Tutorial in my signature. This is an alternative solution.

**JohnnyOnTheSpot** · 07-02-2013, 08:31 PM

Alan, thanks for tipping me off to sumifs and averageifs ... averageifs works like a charm. I added a new column to hold just the year value to get around the date stamp issue.

Now how do I get the hi/lo for the ranges? I want to be able to use multiple criteria to define the range that I get my hi/lo from just like with the 'averageifs' formula, but there does not appear to be anything like a "maxifs" or "minifs" formula as far as I can find.