Excel Formula Help - MEDIAN IF with Array CSE Formulas
Following on from our introduction to CSE Array formulas here, let's look a little more at how this powerful tool can help us work with large data sets.
The sample below is taken from a data base of Baltimore City employee salaries for 2011. The data set runs to over 14,000 entries. Our aim is to find the Median Annual Salary for the corresponding Agency ID. This means that we need a formula that will recognise the individual Agency IDs (assume Job Codes) and line them all up so that the median can be extracted.
We can do this first by creating a new column called Median AID. In H2, we enter the following formula =MEDIAN(IF(C2:C14455="AgencyID",F2:F14455)) remembering to press the CSE keys (Ctrl, Shift, Enter) to achieve the result. "AgencyID" here should be replaced with the location of the ID, in other words C2:
Hit CSE:
Array formulas are great for this. The formula looks through all data in columns C and F before returning the Median for the chosen Agency ID. The next step is to add absolute references so that we can copy down in column H. Remember, absolute references use the symbol $ and essentially anchor the coordinates, so our edited formula will now look like this =MEDIAN(IF($C$2:$C$14455=$C2,$F$2:$F$14455))
As we can see, column coordinates are anchored. The only field without a $ is the row coordinate 2 in the centre of the formula, which must change so that the correct results for the row can be returned in column H. Hit CSE again to return the same result. The formula is now ready to be copied down.
We can test the results by doing a find on a particular AgencyID. If we enter A03031 we see that employee at row 3 is getting slightly less than the median. Hit Find Next a few times and we find that employee at row 7301 with the same Agency ID is getting well over the median rate.
For more help on CSE Array formulas and tips on how to get the best out of your data, get in touch with our experts. For other sources of help using MEDIAN and Array formulas, check the Microsoft Excel Help pages here and here.
