Median with multiple criteria

[Eslava]

I've been trying to get a formula that will calculate a median for base salary if it equals two criteria:
Grade: 15-8 (in column C)
Department: E or S (in column O)
the base salaries are in column I

I put the E's in Column A, S's in Column B, and the Grades in Column C of the worksheet where i need the median.

I tried the following: =MEDIAN(IF((data!$O$3:$O$200=$B10)+(data!$C$3:$C$200=$C10),data!$I$3:$I$200))

because it is such a large data set, I don't want to go in manually and calculate every median for the specific sets.

Is there a way?

 

[Domenic]

To find the median base salary, based on two conditions, try...

=MEDIAN(IF((data!$O$3:$O$200=$B10)*(data!$C$3:$C$2 00=$C10),data!$I$3:$I$200))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Eslava]

I appreciate the try, but it did not work

 

[Eslava]

NYSE - Benchmark Compensation Practices 12-20-2007.xls
	C	D	E	F	G	H	I	J	K	L	M	N	O
2	GRADE	DIVISION	DEPTNAME	JOBCODE	JOBTITLE	FTE	ANNUAL_RT	MID_RT_ANNUAL	Comp-Ratio	2006Bonus	2006Rating	TCC	DEPARTMENT
3	15	MarketSurveillance	SpecialCounsel&TradingCorr	25	VicePresident(15)	1.00	212,800.00	220,000.00	.97	125,000.00	EE	337,800.00	S
4	15	MarketSurveillance	MarketTradingAnalysis2	25	VicePresident(15)	1.00	207,500.00	220,000.00	.94	85,000.00	EE	292,500.00	S
5	15	MarketSurveillance	MarketTradingAnalysis1	25	VicePresident(15)	1.00	201,300.00	220,000.00	.92	75,000.00	MR	276,300.00	S
6	15	Enforcement&Risk	Enforcement&Risk	1	VicePresident(15)	1.00	260,000.00	220,000.00	1.18182	0		260,000.00	E
7	15	Enforcement&Risk	Enforcement	1	VicePresident(15)	1.00	200,000.00	220,000.00	0.90909	140,000.00	EE	340,000.00	E
8	14	MarketSurveillance	MarketSurveillance	26	ManagingDirector(14)	1.00	211,000.00	175,000.00	1.21	95,000.00	EX	306,000.00	S
9	14	MarketSurveillance	TradingExamination	26	ManagingDirector(14)	1.00	210,000.00	175,000.00	1.2	0		210,000.00	S
10	14	MarketSurveillance	SpecialCounsel	27	ChiefCounsel(14)	1.00	192,000.00	175,000.00	1.1	90,000.00	EE	282,000.00	S
11	14	MarketSurveillance	MKS,ENF&Risk	28	ChiefAdminDeputy(14)	1.00	163,700.00	175,000.00	.94	110,000.00	EE	273,700.00	S
12	14	MarketSurveillance	IndustryAnalysis	29	Director(14)	1.00	189,400.00	175,000.00	1.08	45,000.00	EE	234,400.00	S
13	14	MarketSurveillance	SurveillanceDevel&ITSII	29	Director(14)	1.00	189,000.00	175,000.00	1.08	95,000.00	EX	284,000.00	S
14	14	MarketSurveillance	IndustryAnalysis	29	Director(14)	1.00	188,000.00	175,000.00	1.07	50,000.00	MR	238,000.00	S
15	14	MarketSurveillance	SpecialistSurveillanceII	29	Director(14)	1.00	178,300.00	175,000.00	1.02	50,000.00	EE	228,300.00	S
16	14	MarketSurveillance	SpecialInvestigations	29	Director(14)	1.00	170,300.00	175,000.00	.97	40,000.00	MR	210,300.00	S
17	14	MarketSurveillance	MarketSurveillance	29	Director(14)	1.00	167,400.00	175,000.00	.96	35,000.00	EE	202,400.00	S
18	14	MarketSurveillance	IndustryAnalysis	29	Director(14)	1.00	166,400.00	175,000.00	.95	45,000.00	EE	211,400.00	S
19	14	MarketSurveillance	MarketSurveillance	29	Director(14)	1.00	160,400.00	175,000.00	.92	30,500.00	EE	190,900.00	S
20	14	MarketSurveillance	TradingCorrespondence	29	Director(14)	1.00	154,800.00	175,000.00	.88	40,000.00	EE	194,800.00	S
21	14	MarketSurveillance	On-FloorSurveillance	29	Director(14)	1.00	153,300.00	175,000.00	.88	27,500.00	MR	180,800.00	S
22	14	MarketSurveillance	SpecialProducts	29	Director(14)	1.00	151,300.00	175,000.00	.86	22,000.00	EE	173,300.00	S
23	14	MarketSurveillance	IndustryAnalysis	29	Director(14)	1.00	150,000.00	175,000.00	.86	30,000.00	EE	180,000.00	S
24	14	MarketSurveillance	IndustryAnalysis	29	Director(14)	1.00	149,100.00	175,000.00	.85	20,000.00	MR	169,100.00	S
25	14	MarketSurveillance	MemberTrading1	29	Director(14)	1.00	145,000.00	175,000.00	.83	0		145,000.00	S
26	14	MarketSurveillance	MT-AuditTrailI	29	Director(14)	1.00	138,700.00	175,000.00	.79	32,000.00	MR	170,700.00	S
27	14	Enforcement&Risk	Enforcement	2	ManagingDirectorEnforcement(14)	1.00	189,900.00	175,000.00	1.08514	88,000.00	EE	277,900.00	E
28	14	Enforcement&Risk	Risk	3	EnforcementDirector(14)	1.00	185,000.00	175,000.00	1.05714	0		185,000.00	E
29	14	Enforcement&Risk	TradingInv/Specialists	3	EnforcementDirector(14)	1.00	184,000.00	175,000.00	1.05143	57,000.00	MR	241,000.00	E
30	14	Enforcement&Risk	Risk	3	EnforcementDirector(14)	1.00	174,300.00	175,000.00	0.996	50,000.00	EE	224,300.00	E
31	14	Enforcement&Risk	Enforcement	3	EnforcementDirector(14)	1.00	168,600.00	175,000.00	0.96343	65,000.00	EE	233,600.00	E
32	14	Enforcement&Risk	TradingInvest/FloorBrokers	3	EnforcementDirector(14)	1.00	160,700.00	175,000.00	0.91829	52,000.00	MR	212,700.00	E
data

This is the data set I am trying to get the medians from

 

[Oaktree]

Please be more specific than "it did not work". Did it give you an error? Did you get the wrong answer?

Did you confirm Domenic's formula with CTRL+SHIFT+ENTER (so your formula is surrounded by { }'s in the formula bar)?

 

[Eslava]

This is where I'm trying to put the medians. It returned a #NUM error. My apologies for not being specific.

NYSE - Benchmark Compensation Practices 12-20-2007.xls
	A	B	C	D	E	F	G	H
7	Department		Grade		Midpoint		(Enforcement)	(Surveillance)
8					($000)		($000)	($000)
9
10	E	S	15		$220.0
11	E	S	14		175.0
12	E	S	13		152.0
13	E	S	12		129.0
14	E	S	11		107.0
15	E	S	10		89.0
16	E	S	9		74.0
17	E	S	8		62.0
Exhibit 1

 

[Eslava]

I did confirm the formula with control+shift+enter btw...

 

[Domenic]

For E10, it looks like you want the median base salary, where the department is either E or S, and the grade is 15. Is this correct?

 

[Domenic]

For E10, it looks like you want the median base salary, where the department is either E or S, and the grade is 15. Is this correct?

If the above is correct, try...

=MEDIAN(IF(data!$C$3:$C$200=$C10,IF((data!$O$3:$O$200=$A10)+(data!$O$3:$O$200=$B10),data!$I$3:$I$200)))

or

=MEDIAN(IF(data!$C$3:$C$200=$C10,IF(ISNUMBER(MATCH(data!$O$3:$O$200,$A10:$B10,0)),data!$I$3:$I$200)))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Eslava]

Ignore Column E. Column G will contain the medians of the E's and Column H will have the S's, all according to the appropriate grade.