0

My current table looks like this

Company-----year----size-----Exchange

A-----------2000-----80-------A

A-----------2001-----85-------A

B-----------2002------90------C

I want to allocate the companies into two categories "Big" and "Small".

For a particular year, if the companies size is bigger than the median of the size of the companies in that year in Exchange A, will be called "BIG". something like this,

=if([size]>MEDIANX(filter(filter(tbl1,[Year]=A),[Year]),[size]),"Big","Small")

I know the way I used the filters are wrong. I do not know how to do this. Please help me.

Myurathan Kajendran
  • 647
  • 3
  • 8
  • 15

2 Answers2

0

Your question isn't particularly well worded as it's left me wondering if I've made correct assumptions when answering but essentially I think you need something like an array formula with Median.

=IF(C2>MEDIAN(IF($D$2:$D$5="A",IF($B$2:$B$5=B2,$C$2:$C$5))),"Big","Small")

This should give you "Big" if for that year the companies size is bigger than the median of the size of the companies in that year in Exchange A.

Tim Edwards
  • 1,031
  • 1
  • 13
  • 34
  • Thank you for your answer, and I'm sorry for my poor wording. You understood it correctly, but unfortunately, the formula you provided doesn't seem to give me correct answers. – Myurathan Kajendran Jul 07 '16 at 16:07
  • Yeah just checked and you're right, something odd is happening with Median and arrays, if i change it to SUM then it's working fine, you might have to see if anyone else can work out why that's the case. I've just googled it and it appears to be in-line with what other have done e.g. [here](http://spreadsheets.about.com/od/2010statisticalfunctions/qt/2010-04-30-excel-2010-median-function.htm). – Tim Edwards Jul 08 '16 at 08:57
0

I assume you ask for a PowerPivot solution, since your question is tagged as such.

The DAX (a.k.a. Power Pivot) formula MEDIAN() is only available in the preview edition of Excel 2016 (see here: https://msdn.microsoft.com/en-us/library/dn802551.aspx).

However, you can simply build your own median logic using RANKX()

First, add a new column called [RankInExchangeA]:

=If([Exchange]="A",RANKX(FILTER(Table1,[Exchange]="A" && EARLIER([year])=[year]),[size]),Blank())

The EARLIER() function basically means ThisRowsValue().

Now add your desired Big/Small column:

=If([Exchange]="A",If([RankInExchangeA]<=CALCULATE(max([RankInExchangeA]),filter(Table1,EARLIER([year])=[year]))/2,"Small","Big"),"Other Exchange")

EDIT: Added the year condition to the formulas.

EDIT 2: As mentioned in the comments, the following formula would work using MEDIANX():

=IF([size]>MEDIANX(FILTER(Table,[Exchange]="A"&&EARLIER([date])=[date]),[size]),‌​"Big","Small")
Stephan
  • 650
  • 7
  • 16
  • Yes, I ask for a Powerpivot solution. I'm using excel 2016 and it has median() and medianx() functions. In your formula, I think, you did not consider the year filter. – Myurathan Kajendran Jul 08 '16 at 13:08
  • You're absolutely right. I added the year condition in my answer. I would also try to provide a solution with Median(), but since I don't have Excel 2016 available I can't test the formula. – Stephan Jul 08 '16 at 13:48
  • @Djeeramon Thank you so much. How can I edit the solution you have given if I want to allocate them with same criteria (Exchange A and Year) into three groups (top 30%, Middle 40%, and Bottom 30%)? – Myurathan Kajendran Jul 09 '16 at 15:02
  • @Djeeramon I have done Big/Small allocation using following formula. =IF([size]>MEDIANX(FILTER(Table,[Exchange]="A"&&EARLIER([date])=[date]),[size]),"Big","Small") . Thank you so much for your huge help. – Myurathan Kajendran Jul 10 '16 at 15:15
  • To split into multiple groups you can modify the formula using the `Switch()` function. For more details on how to use the formula see: http://www.powerpivotpro.com/2012/06/dax-making-the-case-for-switch/ – Stephan Jul 11 '16 at 12:13
  • I have tried with if function to sort. But I'm not sure how to get the top 30% and other cutoff points. I have asked this as a separate question. If you could have a look, that would be life saving. http://stackoverflow.com/questions/38294515/sorting-portfolios-based-on-criteria-top30-middle-40-and-bottom-30 – Myurathan Kajendran Jul 11 '16 at 13:28