Medians of data

[Pendelfin]

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

 

[T. Valko]

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","Distribution"},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.

 

[Mike H]

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

 

[T. Valko]

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