Salary - medianifs and quartileifs in excel mac

[akswartz85]

Hi, I'm trying to calculate the median salary based on a set of conditions - job code = 42, school type = single site.

Sample data:

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {color:white; font-size:10.0pt; font-weight:700; background:#0070C0; mso-pattern:#0070C0 none;}.xl66 {font-size:10.0pt;}.xl67 {font-size:10.0pt; mso-number-format:"\0022$\0022\#\,\#\#0";}.xl68 {font-family:Calibri, sans-serif; mso-font-charset:0;}--></style>[TABLE="width: 261"]
<!--StartFragment--> <colgroup><col width="87" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 87"]Updated Job Code[/TD]
[TD="class: xl68, width: 87"]Average Salary[/TD]
[TD="class: xl68, width: 87"]Type[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]42[/TD]
[TD="class: xl67, align: right"]$60,000[/TD]
[TD="class: xl68"]Single site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]42[/TD]
[TD="class: xl67, align: right"]$26,000[/TD]
[TD="class: xl68"]Single site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]42[/TD]
[TD="class: xl67, align: right"]$26,500[/TD]
[TD="class: xl68"]Multi site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl67, align: right"]$62,000[/TD]
[TD="class: xl68"]Single site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]49[/TD]
[TD="class: xl67, align: right"]$40,000[/TD]
[TD="class: xl68"]Multi site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]42[/TD]
[TD="class: xl67, align: right"]$45,000[/TD]
[TD="class: xl68"]Multi site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]21[/TD]
[TD="class: xl67, align: right"]$33,000[/TD]
[TD="class: xl68"]Single site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]43[/TD]
[TD="class: xl67, align: right"]$30,000[/TD]
[TD="class: xl68"]Multi site[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]42[/TD]
[TD="class: xl67, align: right"]$51,500[/TD]
[TD="class: xl68"]Single site[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


How do I do that? I've been playing with median(if()) combinations but can't seem to get it.

I also have the same question but with quartiles...

Thanks!

 

[AhoyNC]

Try:
These are array formulas that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	Updated Job Code	Average Salary	Type		Updated Job Code	Type	Median
2	42	$60,000	Single site		42	Single site	$51,500
3	42	$26,000	Single site
4	42	$26,500	Multi site				1st Quartile
5	40	$62,000	Single site				$ 38,750.00
6	49	$40,000	Multi site
7	42	$45,000	Multi site
8	21	$33,000	Single site
9	43	$30,000	Multi site
10	42	$51,500	Single site
Sheet

 

[akswartz85]

Thanks. I'm on a mac - excel 2016 - and I can't get it to calculate the array. Is there a different shortcut?

 

[akswartz85]

@AhoyNC, I'm now getting a #value error. Any thoughts? Used your formula exactly.

 

[AhoyNC]

Not sure on the MAC, but I think it might be COMMAND-SHIFT-RETURN. You will first need to put the formula in edit mode (which is function key F2 on a PC may be the same on mac??).

 

[AhoyNC]

See this link for commands on MAC. Go to the section "Work in cells or formula bar".
https://support.office.com/en-us/ar...44d-962f-4e951a658ccd?ui=en-US&rs=en-US&ad=US

 

[AhoyNC]

I also, assumed that the Job Codes were numeric. If the job codes are text then you will need to enter cell E2 (in the example) as text.