How to calculate the median for a rule based subset of a list (and for different groups of entries in that list)?

[German0815]

Hi to all of you!

I am trying to calculate the median for a rule based subset of a list and do that for different groups of entries in that list. Let me explain in detail:

I have a list of hundreds or thousands of entries with these 4 key columns:

Contract	Submarket	Size in sqm	Rent in € per sqm
1	District A	500	22
2	District C	1,250	37
3	District C	750	30
4	District B	1,000	18
5	District A	4,000	12
...	...	...	...

Now what I want to do is calculate the median rent of the most expensive 3% of contracts for every submarket.

The 3% are calculated by size. Let's say I have 100 contracts in submarket A with a total of 145,000 sqm. Then, I calculate 0.03*145,000 = 4,350 sqm. I then sort the contracts from most expensive to least expensive and look at how many contracts make up 4,350 sqm or more. Let's say the 5 most expensive contracts cross the threshold of 4350 sqm. I then calculate the median rent for those 5 contracts.

But let's say in submarket B the one single contract with the highest rent already makes up more than the 3% of all contracts (or the two most expensive contracts). Then, I want to calculate the median of the three most expensive contracts (which is automatically the second most expensive contract, I know :D ).


Now, imagine having to calculate this for 100 submarkets. This is why I was trying to automate that. My problem is, I don't know how to do this sorting and counting thing and then calculating a median for this specific number of contracts or if this is even possible in Excel at all. Does someone here know how to do this?

Kind regards,
German0815

 

[GraH]

Hi,

I see you have Office 365 so chances are you have Get&Transform on your Ribbon. You might want to give it a try.

1. Select your data range and press CTRL+ T to convert the range to a table. Call the table "tContracts"
2. On the G&T tab choose from Data/Range, confirm you have headers
3. This opens PowerQuery
4. Group By on Contract, and use All Rows as aggregate. Name this field "All".
5. Add a custom column, name it "median", in which you type this formula
   = List.Median ( List.FirstN ( Table.Column ( Table.Sort([All] , {"Size in sqm" , Order.Descending } ) , "Rent in € per sqm" ) , 3 ) )
6. Save and load to Excel as table

Query looks like this (note I give explicit names to the applied steps):

let
    Source = Excel.CurrentWorkbook(){[Name="tContracts"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size in sqm", type number}, {"Rent in € per sqm", Int64.Type}}),
    BufferContracts = Table.Buffer(#"Changed Type"),
    GroupOnContract = Table.Group(BufferContracts, {"Submarket"}, {{"All", each _, type table [Contract=number, Submarket=text, Size in sqm=number, #"Rent in € per sqm"=number]}}),
    MedianTop3 = Table.AddColumn(GroupOnContract, "Median", each
                         List.Median(
                            List.FirstN(
                               Table.Column(
                                  Table.Sort([All],{"Size in sqm", Order.Descending}),
                                "Rent in € per sqm"),
                            3)
                         )
                      )   
in
    MedianTop3

This code can be copied in the advanced editor in the PQ UI.

 

[Eric W]

It can also be done with a formula:

Book1
	A	B	C	D	E	F	G	H	I
1	Contract	Submarket	Size in sqm	Rent in € per sqm			Submarket	Median of top x%	x
2	1	District A	500	22			District A	12	3%
3	2	District C	1,250	37			District B	18
4	3	District C	750	30
5	4	District B	1,000	18
6	5	District A	4,000	12
7	6	District B	1,000	32
Sheet12

Cell Formulas
Range	Formula
H2:H3	H2	=IF(COUNTIFS(B:B,G2,C:C,">="&SUMIFS(C:C,B:B,G2)*I$2)>=3,MEDIAN(IF((B$2:B$100=G2)*(C$2:C$100>=SUMIFS(C:C,B:B,G2)*I$2),D$2:D$100)),LARGE(IF(B$2:B$100=G2,D$2:D$100),2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

This is a very limited sample, but it should work on larger ranges.

Depending on what your update channel is for Excel 365, there are some new functions that would make that a bit simpler. I don't have them yet or I'd show that too.

 

[German0815]

Hi,

I see you have Office 365 so chances are you have Get&Transform on your Ribbon. You might want to give it a try.

1. Select your data range and press CTRL+ T to convert the range to a table. Call the table "tContracts"
2. On the G&T tab choose from Data/Range, confirm you have headers
3. This opens PowerQuery
4. Group By on Contract, and use All Rows as aggregate. Name this field "All".
5. Add a custom column, name it "median", in which you type this formula
   = List.Median ( List.FirstN ( Table.Column ( Table.Sort([All] , {"Size in sqm" , Order.Descending } ) , "Rent in € per sqm" ) , 3 ) )
6. Save and load to Excel as table

Query looks like this (note I give explicit names to the applied steps):

let
    Source = Excel.CurrentWorkbook(){[Name="tContracts"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size in sqm", type number}, {"Rent in € per sqm", Int64.Type}}),
    BufferContracts = Table.Buffer(#"Changed Type"),
    GroupOnContract = Table.Group(BufferContracts, {"Submarket"}, {{"All", each _, type table [Contract=number, Submarket=text, Size in sqm=number, #"Rent in € per sqm"=number]}}),
    MedianTop3 = Table.AddColumn(GroupOnContract, "Median", each
                         List.Median(
                            List.FirstN(
                               Table.Column(
                                  Table.Sort([All],{"Size in sqm", Order.Descending}),
                                "Rent in € per sqm"),
                            3)
                         )
                      )  
in
    MedianTop3

This code can be copied in the advanced editor in the PQ UI.

Hi GraH,

thank you for your very quick reply and help.

I was able to execute this, but sth is wrong with the results. I think you sorted by Size, although the sorting has to be by Rent per sqm, since I want to calculate the median of the contracts with the highest Rent per sqm.

I was also wondering whether this is always calculating the median of the first three or if it calculates the median of the top x, if the 3% rule applies. (?) Based on how I understand the code, it does not. This would still be very useful though, since often the 3% rule is irrelevant and then your solution would work.

I didn't think of Power Query at all, although I have worked with it before. Very interesting approach, since the queries can be used for dynamic data from sql databases as well by "hitting refresh".

I will also try the approach of Eric W.

 

[German0815]

It can also be done with a formula:

Book1
	A	B	C	D	E	F	G	H	I
1	Contract	Submarket	Size in sqm	Rent in € per sqm			Submarket	Median of top x%	x
2	1	District A	500	22			District A	12	3%
3	2	District C	1,250	37			District B	18
4	3	District C	750	30
5	4	District B	1,000	18
6	5	District A	4,000	12
7	6	District B	1,000	32
Sheet12

Cell Formulas
Range	Formula
H2:H3	H2	=IF(COUNTIFS(B:B,G2,C:C,">="&SUMIFS(C:C,B:B,G2)*I$2)>=3,MEDIAN(IF((B$2:B$100=G2)*(C$2:C$100>=SUMIFS(C:C,B:B,G2)*I$2),D$2:D$100)),LARGE(IF(B$2:B$100=G2,D$2:D$100),2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

This is a very limited sample, but it should work on larger ranges.

Depending on what your update channel is for Excel 365, there are some new functions that would make that a bit simpler. I don't have them yet or I'd show that too.

Hi Eric,

if I use this formula, the results are not correct, unfortunately. I am trying to understand why, but I couldn't understand the formula completely. It does give values that are either part of the existing rents or an average of two of them, so it does calculate a median. But it is not the one I need (I have calculated it as an array formula, so that shouldn't be the problem).