|
How can I find a median?
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 |
How can I find a median?
|
How can I find a median?
=MEDIAN(8,8,9,20,20,20)
-- Gary''s Student - gsnu200909 |
How can I find a median?
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 |
How can I find a median?
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 |
How can I find a median?
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 |
How can I find a median?
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 |
How can I find a median?
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 . |
How can I find a median?
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.) |
How can I find a median?
=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.) |
How can I find a median?
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.) . |
How can I find a median?
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 . |
How can I find a median?
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.) . |
How can I find a median?
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. |
How can I find a median?
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! -- Biff Microsoft Excel MVP "Lori Miller" wrote in message ... 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. |
How can I find a median?
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! |
How can I find a median?
Good stuff!
-- Biff Microsoft Excel MVP "Lori Miller" wrote in message ... 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! |
| All times are GMT +1. The time now is 08:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com