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

Hello,

Yes, there is:
http://sulprobil.com/html/statistics...ed_values.html

Regards,
Bernd

=MEDIAN(8,8,9,20,20,20)
--
Gary''s Student - gsnu200909

LisaB -

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

=MEDIAN(A2:A4)

--
Daryl S


"lisab" wrote:

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

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


"lisab" wrote:

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

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...
--
HTH...

Jim Thomlinson


"lisab" wrote:

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


"lisab" wrote:

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

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

Jim Thomlinson wrote:
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.a...excel/200912/1

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


"cabana_boy via OfficeKB.com" wrote:

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

Jim Thomlinson wrote:
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.a...excel/200912/1

.

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


"Jim Thomlinson" wrote:

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


"cabana_boy via OfficeKB.com" wrote:

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

Jim Thomlinson wrote:
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.a...excel/200912/1

.

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.)

=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:A3 6))

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.

--
Biff
Microsoft Excel MVP


"Lori Miller" wrote in message
...
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.)

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?


"T. Valko" wrote:

=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:A3 6))

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.

--
Biff
Microsoft Excel MVP


"Lori Miller" wrote in message
...
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.)




.

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

--
Biff
Microsoft Excel MVP


"Lori" wrote in message
...
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?


"T. Valko" wrote:

=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:A3 6))

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.

--
Biff
Microsoft Excel MVP


"Lori Miller" wrote in message
...
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.)




.