Median and If

**esmcic** · 05-04-2012, 11:18 AM

Hi,
I'm trying to figure out how to calculate the median $ of several departments by using the IF statement but have been getting #Num! messages as well as incorrect numbers. I did hit CTR-SHFT-ENTER at the end of the formula.

=MEDIAN(IF($A:$A="A",$B:$B))

I've attached a sample of my spreadsheet. I used $ in front of the column as the number of rows will always be changing.

Any insight would be appreciated.

Thanks.Excel Help.xlsx

**twiggywales** · 05-04-2012, 11:28 AM

something is up with your sheet A doesnt respond as A

**daddylonglegs** · 05-04-2012, 11:55 AM

You have "trailing spaces" in the column A data. Either get rid of those with Edit > Replace or use TRIM in the formula, e.g.

=MEDIAN(IF(TRIM($A:$A)="A",$B:$B))

confirm with CTRL+SHIFT+ENTER

It will be quicker, though, if you use a smaller range rather than the whole column....

**esmcic** · 05-04-2012, 12:11 PM

Sorry, my mistake.
Should have explained the situation better.
I've downloaded a huge list of data using Live Office.
From this list and on a separate worksheet, I am trying to calculate the median $ for each department. However, when I try to use the IF statement with MEDIAN, I either get an incorrect median, #NUM! or $Value.
I've attached a simple, shortened sample of the list with just 2 columns in 1 worksheet and the summary table in another.

Any help is much appreciated.

Excel Help.xlsx

**ChemistB** · 05-04-2012, 01:35 PM

I get the correct answers using an arrayed

=MEDIAN(IF(TRIM($A:$A)=E1,$B:$B))
For A, E and M in your spreadsheet, I get 30,000, 47,250 and 1923.75. (Excel averages middle two values if the range has an even number in it)
Does that help?

**esmcic** · 05-04-2012, 03:00 PM

Thanks. The formula works fine with the sample spreadsheet but not with my original live office excel report. I'm still getting $0 or an incorrect median. It may have something to do with how the formula is reading the data transferred over from web intelligence via live office.

At least I know how to create the formula. Now I just have to drill down on the live office part.

Thanks for everyone's help.