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!

 

[Domenic]

I don't know whether an add-in exists for the Mac. However, here's a formula that uses native functions...

=MEDIAN(IF(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1))>0,B2:B100))

...confirmed with CONTROL+SHIFT+ENTER.

 

[Aladin Akyurek]

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!

Let's assume a data area that you apply autofilter to and you want the median involving the range P5:P625...

Control+shift+enter, not just enter:

=MEDIAN(IF(SUBTOTAL(2,OFFSET(P5:P625,ROW(P5:P625)-ROW(P5:P625),,1)),P5:P625))

Have also a look at the AGGREGATE function.

 

[mmmmisha]

Thanks to both Aladin and Domenic. I'm assuming that both of these formulae would work on a specific filtered column. A couple of questions:

1. Can you tell me what the syntax of the formula means in "plain english"?
2. Is there a way to modify the formula so that it is "self-defining". i.e. it will cover the filtered range (e.g. B2:B100 or P5:P625) automatically, without my having to manually input the range coordinates?

 

[barry houdini]

I'd try AGGREGATE as Aladin suggests

=AGGREGATE(12,5,range)

12 indicates median, 5 indicates that hidden rows are ignored. You can make range dynamic based on the number of values within the column.....

 

[Aladin Akyurek]

Thanks to both Aladin and Domenic. I'm assuming that both of these formulae would work on a specific filtered column. A couple of questions:

1. Can you tell me what the syntax of the formula means in "plain english"?

Rum MEDIAN on cells that are visible under the filter condition.

2. Is there a way to modify the formula so that it is "self-defining". i.e. it will cover the filtered range (e.g. B2:B100 or P5:P625) automatically, without my having to manually input the range coordinates?

Not quite clear what you mean... If you want the range to be dynamic, convert the area into a table by selecting the are and running the Insert|Table commands.

 

[mmmmisha]

Aladin, houdini: Yes, the results are all within a table -- which is why I can filter them, but Median is not one of the functions available as a "summary" or total of the column, so far as I am aware. This is the fundamental problem I've been grappling with. Running median outside the table gives a median for all values within the column whether visible or not.

Houdini, can you explain what you mean by: You can make range dynamic based on the number of values within the column....."

Thanks!

 

[mikerickson]

Let me address the question of 2011 in general.
MS has promised that the analysis tool-pak will be avaliable as soon as it is ready.
There are some stability issues and VBA help is pretty much non-existant.
The Userforms.Add method is missing.

I've had to use a couple of work-around's. See
http://www.vbaexpress.com/forum/showthread.php?t=34879

 

[Aladin Akyurek]

Aladin, houdini: Yes, the results are all within a table -- which is why I can filter them, but Median is not one of the functions available as a "summary" or total of the column, so far as I am aware. This is the fundamental problem I've been grappling with. Running median outside the table gives a median for all values within the column whether visible or not.

SUBTOTAL does not have the Median option, AGGREGATE does. The MEDIAN(IF(SUBTOTAL... and AGGREGATE formulas should yield the desired values.

Houdini, can you explain what you mean by: You can make range dynamic based on the number of values within the column.....

You can either define (a) a dynamic named range or (b) use an existing functionality. The latter is called the Table functionality: It consists of turning the data area into a table. After having done that, the formulas adjust themselves automatically to the changes in the data area. If you somehow want the former... If we assume a range in column P from P5 on as subject of the median calculation:

=AGGREGATE(12,5,P5:INDEX(P:P,MATCH(9.99999999999999E+307,P:P))

 

[mmmmisha]

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.

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?

SUBTOTAL does not have the Median option, AGGREGATE does. The MEDIAN(IF(SUBTOTAL... and AGGREGATE formulas should yield the desired values.



You can either define (a) a dynamic named range or (b) use an existing functionality. The latter is called the Table functionality: It consists of turning the data area into a table. After having done that, the formulas adjust themselves automatically to the changes in the data area. If you somehow want the former... If we assume a range in column P from P5 on as subject of the median calculation:

=AGGREGATE(12,5,P5:INDEX(P:P,MATCH(9.99999999999999E+307,P:P))