Finding the Average Between Two Data Sets

January 19, 2002 - by Juan Pablo Gonzalez

John Munoz asked:

I'm trying to calculate the median of salaries with a particular job code within a large set of data... something like a Sumif function but to calculate the median. Is there a way to do this?

This is an issue that calls one of the great things in Excel: Array formulas (Or CSE Formulae, as refered here at MrExcel.com, check this tip for hints on CSE Formulae). Let's assume that the Job codes are in A2:A100 and Salaries are in B2:B100. This pretty straightforward formula would give the expected results:

=MEDIAN(IF(A2:A100="JobCode",B2:B100))

Remember, this is a CSE Formula, to enter it you must press at the same time Control Shift Enter, instead of just Enter as regular formulas.