How can I find a median?

[lisab]

Hello,

I want to find a median for team size with data that is currently set up in
the following manner:

Team size # of instances
20 3
9 1
8 2

Is there an easy way to convert the information so that I can use the median
function?
Thanks.
I n

 

[Bernd P]

Hello,

Yes, there is:
http://sulprobil.com/html/statistics_for_weighted_values.html

Regards,
Bernd

 

[Gary''s Student]

=MEDIAN(8,8,9,20,20,20)

 

[Daryl S]

LisaB -

You don't need to arrange the numbers. Just use the function:

=MEDIAN(A2:A4)

 

[lisab]

Let me clarify, I'd like to avoid entering the formula in manually....this is
a "true" example of what I'm trying to convert, and would rather not enter
"1" 79 times, "2" 96 times, etc.:

Team # of
Size instances
1 79
2 96
3 110
5 78
6 62
7 47
8 50
9 32
10 43
11 28
12 26
13 28
14 16
18 7
19 7
20 6
23 4
24 2
26 1
27 1
30 1
32 1
39 1
17 14
4 76
15 16
22 3
16 17
25 1
38 1
31 1
34 1
21 3
33 1

 

[Jim Thomlinson]

So when we add up all of the instances there are 860. So your median is at
the 430th instance. That is 6. Write a helper column function to accumulate
the total and look up the closest match to 430... use index match to do the
lookup...

 

[cabana_boy via OfficeKB.com]

Wouldn't the 430th instance actually be 7? Six ends at the 425th instance (if
my calculator is working correctly).

So when we add up all of the instances there are 860. So your median is at
the 430th instance. That is 6. Write a helper column function to accumulate
the total and look up the closest match to 430... use index match to do the
lookup...

Let me clarify, I'd like to avoid entering the formula in manually....this is
a "true" example of what I'm trying to convert, and would rather not enter

[quoted text clipped - 51 lines]

 

[Jim Thomlinson]

You are correct. By the time you have gone through the 6s you have just past
425 instances, so you would be 5 instances into 7 when you hit 430...
--
HTH...

Jim Thomlinson

Wouldn't the 430th instance actually be 7? Six ends at the 425th instance (if
my calculator is working correctly).

So when we add up all of the instances there are 860. So your median is at
the 430th instance. That is 6. Write a helper column function to accumulate
the total and look up the closest match to 430... use index match to do the
lookup...

Let me clarify, I'd like to avoid entering the formula in manually....this is
a "true" example of what I'm trying to convert, and would rather not enter

[quoted text clipped - 51 lines]

Thanks.
I n

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200912/1

.

 

[Lori Miller]

With the (unordered) data set in the range A3:B36, try either of these:

=MEDIAN(IF(COLUMN(A:IV)<=B3:B36,A3:A36))

(returns 5 if executed with CTRL+SHIFT+ENTER but assumes
ALL frequencies <=255, but could be extended in XL2007.)

=LOOKUP(2,1/FREQUENCY(0.5,PROB(A3:A36,B3:B36/SUM(B3:B36),,A3:A36)),A3:A36)

(returns 5 also - finds the halfway mark in the cumulative frequencies.)

 

[T. Valko]

=MEDIAN(IF(COLUMN(A:IV)<=B3:B36,A3:A36))

assumes ALL frequencies <=255, but could be extended in XL2007.)

Although I haven't tested it you should be able to extend it by transposing
the ROW function:

=MEDIAN(IF(TRANSPOSE(ROW(A1:A10000))<=B3:B36,A3:A36))

I get different results on many tests using the LOOKUP formula. I don't know
how the PROB function works so I can't tell you where it's (or me are) going
wrong.

 

[Lori]

I hadn't thought of using TRANSPOSE(ROW()), thanks for the tip.

In my tests the LOOKUP formula essentially returns the same results
and should be significantly more efficient generally. The method is
equivalent to filling down from C3:

=SUMIF(A$3:A$36,"<="&A3,B$3:B$36)/SUM(B$3:B$36)

and finding the least value >=50%.

In the exact 50% case, MEDIAN returns the middle value, whereas
LOOKUP returns the lower but both should be valid as the median
is not unique then. Were then any other cases when it differed too?

 

[Daryl S]

For this way to work, you will need to sort the original two column by the
team size (first column). Then set your new column to accumulate the counts
before knowing which was the 430th instance. After sorting, the median size
would be 5.

--
Daryl S

You are correct. By the time you have gone through the 6s you have just past
425 instances, so you would be 5 instances into 7 when you hit 430...
--
HTH...

Jim Thomlinson

Wouldn't the 430th instance actually be 7? Six ends at the 425th instance (if
my calculator is working correctly).

So when we add up all of the instances there are 860. So your median is at
the 430th instance. That is 6. Write a helper column function to accumulate
the total and look up the closest match to 430... use index match to do the
lookup...
Let me clarify, I'd like to avoid entering the formula in manually....this is
a "true" example of what I'm trying to convert, and would rather not enter
[quoted text clipped - 51 lines]
Thanks.
I n

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200912/1

.

 

[T. Valko]

Just a few of the differences...

Value Instances
57...1
51...4
1...7
14...2

MEDIAN(IF = 7.5
LOOKUP = 1

Value Instances
1...1
31...4
53...7
37...2

MEDIAN(IF = 45
LOOKUP = 53

Value Instances
2...3
15...4
33...5
45...2

MEDIAN(IF = 24
LOOKUP = 15

 

[Lori Miller]

Just a few of the differences...

But they are all at exactly the 50% point i.e. occur when the number
of observations in the population is even and so the median happens
to fall between categories. Both values are valid in each case cited as
any value between the two points can be a median.

In data samples, there's a common convention to take the average of
the two points but you can also round down or up or use interpolation
as for other "quantiles".

It's up to the OP to decide which value she wants but it's unlikely to
occur anyway in the type of data given, so it's probably a trivial point.

 

[T. Valko]

Ok, I see what's happening now.

MEDIAN(IF returns the literal calculated median which may or may not be a
number from the value range.

LOOKUP returns the middle number of the aggregated instances of the value
range.

For those who may be following this thread, it would look like this:

Value...Instances
43...2
78...1
12...3

MEDIAN(IF = 27.5
LOOKUP = 12

43,43,78,12,12,12

Sorted, we get:

12,12,12,43,43,78

So:

MEDIAN({12;12;12;43;43;78}) = 27.5

The LOOKUP is returning the middle number from 12,12,12,43,43,78. In this
case there is no *exact middle* number so the middle number would be located
between 12 and 43: 12,12,12 middle 43,43,78 so it returns the closest
number that is less than "middle" = 12.

See, you can learn something new every day!

 

[Lori Miller]

Yup, your description was a lot clearer.
I should do better as a former stats tutor <g>.

I just noticed though your second example from earlier
takes the upper value of 53 instead of the lower one.
It looks like this is because the result of the PROB
calculation is rounded to just larger than 0.5 internally.

Maybe more consistent and easier to follow to use this
which does return the lower value of 37 in that case:

=LOOKUP(2,1/FREQUENCY(SUM(B3:B36)/2,SUMIF(A3:A36,"<="&A3:A36,B3:B36)),A3:A36)

You can learn two new things if you're lucky!

 

[T. Valko]

Good stuff!