If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Medians of data
Hi
Please can anyone let me know what the formula would be to let me know the median of a range of numbers depending on the types - ie Type Number Distribution 1 Distribution 1 Distribution 2 Distribution 2 Distribution 3 Distribution 4 Distribution 5 Marketing 1 Marketing 2 Marketing 3 Marketing 3 Marketing 4 Sales 1 Sales 2 Sales 2 Sales 2 Sales 2 Sales 3 Sales 4 Vending 2 Vending 2 Vending 3 Vending 3 Vending 3 Vending 5 What I need to do - as my data is rather more than the example and will not be sorted as above - is show the median for Sales & Distribution would be 2 and Vending & Manufacturing 3. Please advise and thanks for your time Pen |
#2
|
|||
|
|||
Medians of data
show the median for Sales & Distribution would be 2
Do you mean the median of the *combined values* for Sales & Distribution or do you mean the separate median for Sales and the separate median for Distribution? For the *separate* medians. All formulas are rray entered** : =MEDIAN(IF(A2:A26="Sales",B2:B26)) =MEDIAN(IF(A2:A26="Distribution",B2:B26)) For the *combined values* median: =MEDIAN(IF(ISNUMBER(MATCH(A2:A26,{"Sales","Distrib ution"},0)),B2:B26)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Pendelfin" wrote in message ... Hi Please can anyone let me know what the formula would be to let me know the median of a range of numbers depending on the types - ie Type Number Distribution 1 Distribution 1 Distribution 2 Distribution 2 Distribution 3 Distribution 4 Distribution 5 Marketing 1 Marketing 2 Marketing 3 Marketing 3 Marketing 4 Sales 1 Sales 2 Sales 2 Sales 2 Sales 2 Sales 3 Sales 4 Vending 2 Vending 2 Vending 3 Vending 3 Vending 3 Vending 5 What I need to do - as my data is rather more than the example and will not be sorted as above - is show the median for Sales & Distribution would be 2 and Vending & Manufacturing 3. Please advise and thanks for your time Pen |
#3
|
|||
|
|||
Medians of data
Maybe
=MEDIAN(IF((A1:A25="Sales"),(A1:A25="Distribution" ),B1:B25)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Pendelfin" wrote: Hi Please can anyone let me know what the formula would be to let me know the median of a range of numbers depending on the types - ie Type Number Distribution 1 Distribution 1 Distribution 2 Distribution 2 Distribution 3 Distribution 4 Distribution 5 Marketing 1 Marketing 2 Marketing 3 Marketing 3 Marketing 4 Sales 1 Sales 2 Sales 2 Sales 2 Sales 2 Sales 3 Sales 4 Vending 2 Vending 2 Vending 3 Vending 3 Vending 3 Vending 5 What I need to do - as my data is rather more than the example and will not be sorted as above - is show the median for Sales & Distribution would be 2 and Vending & Manufacturing 3. Please advise and thanks for your time Pen |
#4
|
|||
|
|||
Medians of data
=MEDIAN(IF((A1:A25="Sales"),(A1:A25="Distribution "),B1:B25))
That returns an incorrect result. It'll work this way. Array entered: =MEDIAN(IF((A2:A26="Sales")+(A2:A26="Distribution" ),B2:B26)) -- Biff Microsoft Excel MVP "Mike H" wrote in message ... Maybe =MEDIAN(IF((A1:A25="Sales"),(A1:A25="Distribution" ),B1:B25)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Pendelfin" wrote: Hi Please can anyone let me know what the formula would be to let me know the median of a range of numbers depending on the types - ie Type Number Distribution 1 Distribution 1 Distribution 2 Distribution 2 Distribution 3 Distribution 4 Distribution 5 Marketing 1 Marketing 2 Marketing 3 Marketing 3 Marketing 4 Sales 1 Sales 2 Sales 2 Sales 2 Sales 2 Sales 3 Sales 4 Vending 2 Vending 2 Vending 3 Vending 3 Vending 3 Vending 5 What I need to do - as my data is rather more than the example and will not be sorted as above - is show the median for Sales & Distribution would be 2 and Vending & Manufacturing 3. Please advise and thanks for your time Pen |
Thread Tools | |
Display Modes | |
|
|