Median function add-in for Excel 2011 (Mac)?

[mmmmisha]

Hi: I've been using Excel 2007 and an add-in from Digdb that allows me to calculate medians within filtered tables, since that is not one of the options currently available within Excel (on the total line at the end of the table).

Now that Microsoft has released Excel 2011, I'm hoping to gravitate my large database over to it since that's my native platform. I'm wondering whether anyone is aware of an add-in that can calculate medians within filtered tables for the Mac version.

More generally, is anyone familiar enough with both programs to opine as to whether the mac version is now as fully functional as the windows version?

Many thanks!

 

[Aladin Akyurek]

This is greatt! And I see that Aggregate is a completely new function. Now that I understand the syntax, maybe the simplest thing is to simply name the range within the table and have the aggregate function refer to it.

The subranges in an area turned into a table can be indeed assigned names and the names can be referred to in the formulas.

Should I put the Aggregate function within the table (I think one can do that as an alternative to the standard pull-down summing formulae, or can it also rest outside the table?

I'm not sure I understand this right... If you are referring to the Total Row bit within the table functionality, it seems we have SUBTOTAL there, but not AGGREGATE (Excel 2010). You can implement AGGREGATE outside the table without any problem.

 

[mmmmisha]

Thanks, I tried playing around with the formula a bit and it seems to work very easily. In fact, since I only have one table on the entire sheet (it's got about 34,000 rows), I believe I can just define the entire column as the range.

Here's the $64,000 question: The data covers about 6 years of real estate sales transactions that occurred on various dates. How would I construct a formula that returns the MONTHLY medians for the entire period or a portion thereof?

If AGGREGATE were available within a pivot table, this would be easy, but of course it's not.

M

 

[Aladin Akyurek]

Thanks, I tried playing around with the formula a bit and it seems to work very easily. In fact, since I only have one table on the entire sheet (it's got about 34,000 rows), I believe I can just define the entire column as the range.

Here's the $64,000 question: The data covers about 6 years of real estate sales transactions that occurred on various dates. How would I construct a formula that returns the MONTHLY medians for the entire period or a portion thereof?

If AGGREGATE were available within a pivot table, this would be easy, but of course it's not.

M

Did you use the Table functionality or not, that is, run Insert|Table on the data area? It's not the same thing as a pivot table...

 

[mmmmisha]

yes

 

[Aladin Akyurek]

yes

Right. Would you post a tiny sample (e.g., 10 row by 4 column)
along with the desired monthly medians?

 

[mmmmisha]

It looks I can only insert an image right? Or I could send you a small section of the DB as an excel file via email if you like. Alternatively, you could take a look at my blog as that will give you an idea of what I need to do with the data. Here is a link to a blog post with a chart generated from monthly medians that I compile from home sales records for the whole of San Francisco. Frequently what I do is generate a chart for a particular sub-area of San Francisco, or a sub-type of property.

I can do all of this fairly easily by using the "roll-up" function offered by DigDB, for which I pay an annual license fee. However, this means I have to boot into Windows via Parallels and that's a pain

Thank you very much Aladin for your level of interest. It's quite unexpected, and very much appreciated.

 

[Aladin Akyurek]

You are welcome.

It looks I can only insert an image right?...

Images are not that useful. One way to post data is:

http://www.mrexcel.com/forum/showpost.php?p=2198045

By the way...

=MEDIAN(IF(Range=Cond,Values))

confirmed with control+shift+enter, not just enter, would be a basic formula
for a conditional median calculation.