svgleeson

You could just add another column. There might be an array solution, but I wouldn't recommend it as you will spend ages trying to work out what it is doing.

So my solution would be.
Column B = If(A...="Revenue",A...,"")
Median = Large (Column A, Countif (Column B,">0")/2)

cityboy

no Pivot Tables do not allow medians.

I got this to work, but I am just using a toy dataset. There must be something wrong with your data at some point. I used array-entering as well.

does Percentile help? Same formula, just use Percentile instead of Median and add ,0.5)

=PERCENTILE(IF($A$2A$13="revenue",B2:B13),0.5)

so my question to you would be what's in your data that causes Median to fail:

- Logical values and text representations of numbers that you type directly into the list of arguments are counted.

- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

- Arguments that are error values or text that cannot be translated into numbers cause errors.

adehbone

I don't understand why you have to create such a complicated formula for this. I just tried with the formula Numi provided "=MEDIAN(IF($A100A200="Revenue",$G100G200))", and press CTRL+SHIFT+ENTER, instead of just press ENTER. Please see a simpler version below.

A G
revenue 1
revenue 2
revenue 3
revenue 4
cost 5
cost 6

{=MEDIAN(IF(A1:A6="Revenue",G1:G6))} 2.5


It did what Numi wanted to to. Am I missing something here?



Edited 1 time(s). Last edit at Monday, August 15, 2011 at 05:02PM by dhyun3.

bkballa

I didn't and haven't tried his formula. I assumed it doesn't work because he couldn't get it to work. If it does work, then we're either missing something, or he has data errors.

But hey, if it works it works.

badonkus

Howd, TheBigBean, dhyun3 -- all your suggestions make sense. However there is some issue with my Excel sheet, perhaps, that causes this command to return zero for some cells, when I know that the cells don't contain zero values (there are blank cells, yes, but they're not zero). It's weird why it does this?

Howd, thanks for your note -- I will send you the spreadsheet later tonight. The commands seem to work fine when I use AVERAGE but for some reason, MEDIAN will return values of Zero when it clearly should not.

Thank to all for their help.



Edited 1 time(s). Last edit at Monday, August 15, 2011 at 05:21PM by numi.