creating new function in Excel for Medians

[Opsimist]

Hi,
I have Excel 2007, there's the "averageifs" function... I'm trying to create
a new function which is similar to that one but the "medians" not with
averages.
That means I want to search for medians in a data range by multiple criteria.
Please help me with syntax.

Thank you very much.

 

[Jim Cone]

You need to provide more information...
Are the medians already determined and listed or do you want
to search thru sets of data to establish the medians?
What kind of criteria?
Samples of data and expected results would be best.
Do you want a formula solution or a vba solution?
What have you tried so far that doesn't work? - examples?
Have you searched for a solution?
--
Jim Cone
Portland, Oregon USA



"Opsimist" <[email protected]>
wrote in message
Hi,
I have Excel 2007, there's the "averageifs" function... I'm trying to create
a new function which is similar to that one but the "medians" not with
averages.
That means I want to search for medians in a data range by multiple criteria.
Please help me with syntax.
Thank you very much.

 

[Bernd P]

Hello,

Google for excel vba medianif
and you will find some suggestions, for example mine:
http://www.sulprobil.com/html/medianif.html

Regards,
Bernd

 

[Opsimist]

Well, I have a list of data from an experiment I did which includes subjects,
different stimuli and reaction time to the stimuli (the stimuli comes in
numbers). Each subject had a number of stimuli, and he had to repeat the same
thing 10 times. for example: subject A had 100 stimuli, from which 10 stimuli
where number 1, another 10 where number 2 and so on... The list of the data
is a big array that includes the following:
Column A - the subject's name (in a coded form), Column B is the stimulous
number, and Column C is the reaction time to the stimulous.
I'm searching for a formula (i don't care if the solution is in a form of a
formula or VBA), that would return the median of the reaction time to a
certain stimuli for a specific subject.
something in the form of 'if Subject = A' and 'Stimuli = 1" then return the
median of the reaction times.
thank you

 

[Bernd P]

Hello,

I published a solution here:
http://www.sulprobil.com/html/medianif.html

Regards,
Bernd

 

[Jim Cone]

Titles Start in Cell A1
Data starts in Cell A3
'--
Name Stimuli Time Helper Median

Smith 1 101 1
Smith 1 94 2
Smith 1 99 0 99
Smith 5 40 1
Smith 5 55 2
Smith 5 50 0 50
Jones 14 65 1
Jones 14 66 2
Jones 14 59 3
Jones 14 67 0 65.5
Adams 88 7 1
Adams 88 6 2
Adams 88 5 0 6
'--
Formula starting in D3 (Helper):
=IF(A3&B3=A4&B4,D2+1,0)

Formula starting in E3 (Median):
=IF(D3=0,MEDIAN(OFFSET(C3,-D2,0,D2+1,1)),"")

Seems to work.
--
Jim Cone
Portland, Oregon USA



"Opsimist" <[email protected]>
wrote in message
Well, I have a list of data from an experiment I did which includes subjects,
different stimuli and reaction time to the stimuli (the stimuli comes in
numbers). Each subject had a number of stimuli, and he had to repeat the same
thing 10 times. for example: subject A had 100 stimuli, from which 10 stimuli
where number 1, another 10 where number 2 and so on... The list of the data
is a big array that includes the following:
Column A - the subject's name (in a coded form), Column B is the stimulous
number, and Column C is the reaction time to the stimulous.
I'm searching for a formula (i don't care if the solution is in a form of a
formula or VBA), that would return the median of the reaction time to a
certain stimuli for a specific subject.
something in the form of 'if Subject = A' and 'Stimuli = 1" then return the
median of the reaction times.
thank you