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.

Rasec

Have you tried it as an array function (ctrl, shift, enter)?

grharmeyer

yes it works for some columns but not for all...

comp_sci_kid

Thanks soddy, that's what I'm trying to do -- then how would calculate the median of all of those data points?

I think maybe there is something wrong with my excel file since it seems to work for some columns but not all. Or perhaps something that prevents the formula for working. That is to say,I have Company A, B, C, D, E, etc all on the same spreadsheet, but your VLOOKUP only grabs the Revenue data for Company A.

bdavi77962

Numi, don't bother using this. I tried it on one of my own spreadsheets and it throws out incorrect values.

I'll try to come up with something else.

HuskyGrad2010

no problem soddy...thanks though. let me know if you want me to send over my spreadsheet. I noticed that with arrays, AVERAGE yields the correct values but MEDIAN does not.

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!

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.

Howd

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

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

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.