German0815
New Member
- Joined
- May 29, 2020
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
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:
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
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