database median function????

[schmidtjoseph]

Hello all,
My data has many criteria creating different classes or conditions that need
to be grouped together before I perform any statistics on it. As such, I
typically use functions like Daverage, Dsum, and Dcount. However, I need to
calculate the median value for each of these different conditions. Since I
can't find a database median function, I have resorted to presorting the data
and performing the median function on each individual condition (which after
the sort range from 8-15 cells long and this inconsistently varies depending
upon the condition). Whereas this works, it takes forever and it is highly
error prone. Is there any way to do something like a database median
function? Thanks in advance.

 

[Bob Phillips]

=MEDIAN(IF(B2:B20="criteria",A2:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[schmidtjoseph]

Thank you so much! Excellent!
Currently I am using
{=MEDIAN(IF('s1'!X:X=1,IF('s1'!Y:Y=1,IF('s1'!Z:Z=1,'s1'!W:W))))} and it seems
to match when sort the data and do the median function by hand. So, I think
this is right! You would think with all of the database functions excel
already has, a database median function would be available.