Top, middle and bottom averages

Copper Contributor

Trying to do top middle and bottom averages with a condition.

 

I have rents of differens areas and want to create a conditional average rent based on the area. Other than that I would also want to make the rent in low, middle and top averages to show the different levels in each area. Please check the attached filed as example.

 

Many thanks

 

 

 

4 Replies

@Benwi123 I believe you need to define first what is High, Medium and Low. Add a column to the table and create a pivot table on that one. See attached. Is that something you are after?

Screenshot 2021-10-26 at 16.09.07.png

Thank you for the fast response. I was thinking if it's possible, with code, to sort or make a "normal distribution" of the observations of each area and thereafter make a lower, middle and high average of the distribution sorted 0-33% (which will be average of lower), 33-67% (which will be average of middle), 67-100% (which will be average of higher) of the observations.

I'm afraid that your way of doing it could be a lot of work if the observations get to many

Hope this makes sense, I don't know if this even is possible

@Benwi123 Understand, but statistics is not my speciality and I'm not really up to speed on how to create such a normal distribution in Excel.

I understand. The problem is also that I don't want to determine what the lower, middle and higher levels are - want it to set the levels according to the inputs if that would be possible

Anyways, thank you for your time