CFA论坛 - Powered by Discuz! Board

标题: Excel help / conditional medians [打印本页]

作者: edgeon 时间: 2011-10-11 15:49 标题: Excel help / conditional medians

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($A100

A200="Revenue",$G100

G200))

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 时间: 2011-10-11 15:54

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

作者: grharmeyer 时间: 2011-10-11 16:00

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

作者: comp_sci_kid 时间: 2011-10-11 16:17

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 时间: 2011-10-11 16:22

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 时间: 2011-10-11 16:28

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 时间: 2011-10-11 16:33

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 时间: 2011-10-11 16:39

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

give this a shot:

=INDEX($A$100

G$200,SMALL(IF($A$100

A$200="Revenue",ROW($A$100

A$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 时间: 2011-10-11 16:44

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

=MEDIAN(IF(ISERROR($G100

G200),"",IF($A100

A200="Revenue",$G100

G200,"")))

作者: noel 时间: 2011-10-11 16:50

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.

作者: svgleeson 时间: 2011-10-11 16:55

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 时间: 2011-10-11 17:01

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$2

A$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 时间: 2011-10-11 17:06

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($A100

A200="Revenue",$G100

G200))", 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 时间: 2011-10-11 17:18

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 时间: 2011-10-11 17:23

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.

欢迎光临 CFA论坛 (http://forum.theanalystspace.com/)