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.

[TABLE="width: 255"]
<tbody>[TR]
[TD][ColumnA]
Position #[/TD]
[TD][ColumnB]
Base Salary[/TD]
[TD][ColumnC]
Median Salary[/TD]
[/TR]
[TR]
[TD]Position #1 [/TD]
[TD] $204,750.00[/TD]
[TD] $204,750.00[/TD]
[/TR]
[TR]
[TD]Position #2 [/TD]
[TD] $155,000.00[/TD]
[TD] $155,112.00[/TD]
[/TR]
[TR]
[TD]Position #2 [/TD]
[TD] $155,224.00[/TD]
[TD] $155,112.00[/TD]
[/TR]
[TR]
[TD]Position #3 [/TD]
[TD] $208,464.00[/TD]
[TD] $208,464.00[/TD]
[/TR]
[TR]
[TD]Position #4[/TD]
[TD] $175,000.00[/TD]
[TD] $189,037.50[/TD]
[/TR]
[TR]
[TD]Position #4[/TD]
[TD] $203,075.00[/TD]
[TD] $189,037.50[/TD]
[/TR]
[TR]
[TD]Position #5[/TD]
[TD] $195,209.00[/TD]
[TD] $199,142.00[/TD]
[/TR]
[TR]
[TD]Position #5[/TD]
[TD] $203,075.00[/TD]
[TD] $199,142.00[/TD]
[/TR]
[TR]
[TD]Position #6[/TD]
[TD] $180,000.00[/TD]
[TD] $203,075.00[/TD]
[/TR]
[TR]
[TD]Position #6[/TD]
[TD] $203,075.00[/TD]
[TD] $203,075.00[/TD]
[/TR]
[TR]
[TD]Position #6[/TD]
[TD] $231,750.00[/TD]
[TD] $203,075.00[/TD]
[/TR]
[TR]
[TD]Position #7[/TD]
[TD] $165,661.00[/TD]
[TD] $203,075.00[/TD]
[/TR]
[TR]
[TD]Position #7[/TD]
[TD] $203,075.00[/TD]
[TD] $203,075.00[/TD]
[/TR]
[TR]
[TD]Position #7[/TD]
[TD] $230,000.00[/TD]
[TD] $203,075.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Position #​

[/td][td="bgcolor:#F3F3F3"]

Base Salary​

[/td][td="bgcolor:#F3F3F3"]

Median Salary​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Position #1[/td][td]

$204,750.00​

[/td][td="bgcolor:#E5E5E5"]

$204,750.00​

[/td][td]C2: {=IF(A2=A1, "", MEDIAN(IF($A$2:$A$15=A2, $B$2:$B$15)))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Position #2[/td][td]

$155,000.00​

[/td][td="bgcolor:#E5E5E5"]

$155,112.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Position #2[/td][td]

$155,224.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Position #3[/td][td]

$208,464.00​

[/td][td="bgcolor:#E5E5E5"]

$208,464.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Position #4[/td][td]

$175,000.00​

[/td][td="bgcolor:#E5E5E5"]

$189,037.50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Position #4[/td][td]

$203,075.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Position #5[/td][td]

$195,209.00​

[/td][td="bgcolor:#E5E5E5"]

$199,142.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Position #5[/td][td]

$203,075.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Position #6[/td][td]

$180,000.00​

[/td][td="bgcolor:#E5E5E5"]

$203,075.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]Position #6[/td][td]

$203,075.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Position #6[/td][td]

$231,750.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Position #7[/td][td]

$165,661.00​

[/td][td="bgcolor:#E5E5E5"]

$203,075.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Position #7[/td][td]

$203,075.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]Position #7[/td][td]

$230,000.00​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[/table]

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.