Median if function

[tis1337]

Hi All,

I need to make an if based median calculation.

e.g. I have a list of numbers in column B and a critera range located column A. I will need to calculate the median of the numbers in B that meets the criteria in A.

Any help would be highly appriciated

[rcm]

please send a worksheet with the sample input and its expected results..

[loginjmor]

Hi -

You could try the following:

=MEDIAN(OFFSET(B1,MATCH(D2,A1:A11,0)-1,0,COUNTIF(A1:A11,D2),1))

Where Criteria is in A1:A11, Values are in B1:B11, Criteria is in D2.

I have attached a sample worksheet.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	Region	Value	------		Median
2	East	13		North	50
3	North	50		East	28.5
4	West	1		South	33.5
5	West	5		West	5
6	North	67
7	East	44
8	South	59
9	North	37
10	South	8
11	West	9

This array formula** entered in E2 and copied down:

=MEDIAN(IF(A$2:A$11=D2,B$2:B$11))

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

[tis1337]

Worked like a charm Tony - Thanks for your great formula!

[Tony Valko]

You're welcome. Thanks for the feedback!