Conditional Median of Specific Cells

[jbolduc123]

Hi,
I hope this makes sense..... I'm trying to calculate the median of salaries that meet a set of criteria. I've looked at other threads, and tried the {=Median(if(A1:A16=criteria,B1:B16))}. This formula works, but it's not returning the median I'm looking for.

For example, I want to find the separate Median Base Salary of all positions titled Position #1 , all the positions titled Position #2 , Position #3 , etc. (in the table below), without having to manually select the range in column B. Any and all help is greatly appreciated.

[ColumnA] Position #	[ColumnB] Base Salary	[ColumnC] Median Salary
Position #1	$204,750.00	$204,750.00
Position #2	$155,000.00	$155,112.00
Position #2	$155,224.00	$155,112.00
Position #3	$208,464.00	$208,464.00
Position #4	$175,000.00	$189,037.50
Position #4	$203,075.00	$189,037.50
Position #5	$195,209.00	$199,142.00
Position #5	$203,075.00	$199,142.00
Position #6	$180,000.00	$203,075.00
Position #6	$203,075.00	$203,075.00
Position #6	$231,750.00	$203,075.00
Position #7	$165,661.00	$203,075.00
Position #7	$203,075.00	$203,075.00
Position #7	$230,000.00	$203,075.00

<tbody>
</tbody>

 

[shg]

	A​	B​	C​	D​
1​	Position #​	Base Salary​	Median Salary​
2​	Position #1	$204,750.00​	$204,750.00​	C2: {=IF(A2=A1, "", MEDIAN(IF($A$2:$A$15=A2, $B$2:$B$15)))}
3​	Position #2	$155,000.00​	$155,112.00​
4​	Position #2	$155,224.00​
5​	Position #3	$208,464.00​	$208,464.00​
6​	Position #4	$175,000.00​	$189,037.50​
7​	Position #4	$203,075.00​
8​	Position #5	$195,209.00​	$199,142.00​
9​	Position #5	$203,075.00​
10​	Position #6	$180,000.00​	$203,075.00​
11​	Position #6	$203,075.00​
12​	Position #6	$231,750.00​
13​	Position #7	$165,661.00​	$203,075.00​
14​	Position #7	$203,075.00​
15​	Position #7	$230,000.00​

The results you show area are correct -- what's your question?

 

[StephenCrump]

C2: {=MEDIAN(IF(A$1:A$16=A2,B$1:B$16))}

works fine for me.

 

[StephenCrump]

The results you show area are correct -- what's your question?

At a guess, I'd say the results shown are those required.

But the original formula didn't lock rows 1:16, hence would have thrown the occasional wrong result when copied down the column.

 

[jbolduc123]

Thanks so much for the quick response! My challenge, is that Column C can't have blanks. For example, there are 2 Position #2 lines, and they both need to reflect the same median (for their position title only).

Is this doable?

Many thanks!

 

[jtakw]

Hi,

As mentioned by shg and Stephen, your formula is Already doing that, so still don't understand your problem...

Book1
	A	B	C
1	Position #	Base Salary	Your Formula
2	Position #1	$204,750.00	$204,750.00
3	Position #2	$155,000.00	$155,112.00
4	Position #2	$155,224.00	$155,112.00
5	Position #3	$208,464.00	$208,464.00
6	Position #4	$175,000.00	$189,037.50
7	Position #4	$203,075.00	$189,037.50
8	Position #5	$195,209.00	$199,142.00
9	Position #5	$203,075.00	$199,142.00
10	Position #6	$180,000.00	$203,075.00
11	Position #6	$203,075.00	$203,075.00
12	Position #6	$231,750.00	$203,075.00
13	Position #7	$165,661.00	$203,075.00
14	Position #7	$203,075.00	$203,075.00
15	Position #7	$230,000.00	$203,075.00
Sheet25

Cell Formulas
Range	Formula
C2	{=MEDIAN(IF(A$2:A$15=A2,B$2:B$15))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jbolduc123]

AHA! I've got it!!! I was completely missing the CTRL+SHIFT+ENTER piece!

Thank you so much for all your help! :D

JB

 

[jtakw]

Great, yes, array formulas need to be enter by CSE, glad you got it working.

You're welcome, welcome to the forum.