LinkBack URL
About LinkBacks
I have a large data set (over 25K lines) containing H, M, or L - I need to calculate the median of these. If I recall correctly, Median only calculates on numerical values, is this correct? Can you anyone please help with a formula on this??
Median of High, Medium, Low - Excel 2007
I have a large data set (over 25K lines) containing H, M, or L - I need to calculate the median of these. If I recall correctly, Median only calculates on numerical values, is this correct? Can you anyone please help with a formula on this??
Can you post a sample workbook?
Column A Column BColumn C
H 1 H
M 2 M
L 3 L
H 4 H
M 5 M
L 1 L
H 2 H
M 3 M
L 4 L
H 5 H
M 1 M
L 2 L
H 3 H
M 4 M
L 5 L
H 1 H
M 2 M
L 3 L
H 4 H
M 5 M
L 1 L
H 2 H
M 3 M
L 4 L
Median? Median? median?
=MEDIAN(IF(A1:A25000="H",B1:B2500)) For H
=MEDIAN(IF(A1:A25000="L",B1:B2500)) for L
=MEDIAN(IF(A1:A25000="M",B1:B2500)) for M.
all three of the above equation are confirmed with ctrl+Shift+Enter. They are array equations.
Adjust range to fit your data.
Regards,
Vandan
The problem I am running into is that none of the columns are dependent upon each other - meaning Column B has nothing to do with column A. They are all independent of each other. The H,M,L columns is not weighted.
Do you think its possible to just take the median of each separately, Vandan?
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.
You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file.
To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
HTH
Regards, Jeff
What numeric value does H, M, L represent? I don't think I understand the question completely. How do you take medium of letters?
Median is the middle value as you point out and yes it requires numerical data.
For non-numaric data, I guess you can assign 1 to L 2 to M and 3 to H
Column D would be: =if(b2 = ""L",1,if(B2="M",2,3))
then median would be
=INDEX({"L","M","H"},ROUND(MEDIAN(D2:D25000),0))
It represents a risk. High Medium or Low. They are not tied to a number. So for example 20 different risk ratings high med or low. Whats the average risk?
This is how I was thinking of doing it. Thank you - Is there a way I can do this all in one formula statement? Basically telling excel to apply a numerical number and then take the median of those?Originally Posted by vandan_tanna
=INDEX({"L","M","H"},ROUND(MEDIAN(IF(B1:B25000 ="L",1,IF(B1:B25000 ="M",2,3))),0))
confirmed with ctrl+shift+enter
Vandan - is there a way to keep this formula static, so when I am sorting by another column, its still calculates the median based on my sort?
>>calculates the median based on my sort
I do not understand. Median calculation is not impacted by / does not require sorting.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks