Excel help / conditional medians companies, including, company, someone, median

edgeon

Hi guys,

I need some Excel help and figured someone here might have some insight. Specifically I need to conditional medians for a spreadsheet that I have with a bunch of financial data pertaining to acquisitions. Specifically this company has made 10 acquisitions, and I'm trying to find the median of revenues for each company (and also COGS, OpEx, etc.).

A basic P&L, including revenues, exists on the same spreadsheet for these companies, which occupy rows 100 to 200 on this sheet. In column A, I have the P&L line item categories; column G corresponds to the dollar amounts for that line item in year 2010. I'm trying to use the following formula, but it gives me a #VALUE! error. Any thoughts on what I'm doing wrong?

=MEDIAN(IF($A100A200="Revenue",$G100G200))

Send me an e-mail if it helps for you to see the actual spreadsheet...thank you. numi.advisory@gmail.com



Edited 1 time(s). Last edit at Monday, August 15, 2011 at 11:13AM by numi.

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.

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.

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.

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.

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)

noel

Seems like this is what Pivot tables were made for.

I can't remember if Excel has Median as a pivot table option, but it seems likely.

This kind of conditional stuff is sort of at the limits of what Excel can do without bending over backwards, but you probably know that already. I guess another way is to write a macro.

Howd

Sent you an email see if this works -- ctrl-shift-enter for array

=MEDIAN(IF(ISERROR($G100G200),"",IF($A100A200="Revenue",$G100G200,"")))

Swanand

I think I understand what you're trying to do.

give this a shot:

=INDEX($A$100G$200,SMALL(IF($A$100A$200="Revenue",ROW($A$100A$200)),ROW(1:1)),7)

Use ctrl+shift+enter when you type it in to make it an array. Then you'll have to drag it down in order to get the revenue numbers in a row.

I would put this all in one separate column (for example column X).

Then you simply do a =median(X1:X100) or whatever it is and you should have the median number.

Edit: This basically looks at column A, finds the word revenue, and brinks back the corresponding number in column G. The array allows you to bring all of these revenue numbers into one column and right after one another which then makes calculations easy.

If this doesn't work, I'll try to play around with it, but I don't have access to private emails here at work so I can't check the file even if you do send it.



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

mp3bu

I can only think of a long winded way to get this data that may or may not work. I'm sorry, I can't check because I'm not at a machine for the rest of the day.

Set up a table, with the metrics you need the medians for in the rows and company's in the columns. Use the formula I mentioned above, changing the lookup array for each company. Add a column at the end of the table to calculate the median.

As I said, long winded and may or may not work!