Top, middle and bottom averages

%3CLINGO-SUB%20id%3D%22lingo-sub-2885948%22%20slang%3D%22en-US%22%3ETop%2C%20middle%20and%20bottom%20averages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2885948%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20do%20top%20middle%20and%20bottom%20averages%20with%20a%20condition.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20rents%20of%20differens%20areas%20and%20want%20to%20create%20a%20conditional%20average%20rent%20based%20on%20the%20area.%20Other%20than%20that%20I%20would%20also%20want%20to%20make%20the%20rent%20in%20low%2C%20middle%20and%20top%20averages%20to%20show%20the%20different%20levels%20in%20each%20area.%20Please%20check%20the%20attached%20filed%20as%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2885948%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2886142%22%20slang%3D%22en-US%22%3ERe%3A%20Top%2C%20middle%20and%20bottom%20averages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2886142%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1196319%22%20target%3D%22_blank%22%3E%40Benwi123%3C%2FA%3E%26nbsp%3BI%20believe%20you%20need%20to%20define%20first%20what%20is%20High%2C%20Medium%20and%20Low.%20Add%20a%20column%20to%20the%20table%20and%20create%20a%20pivot%20table%20on%20that%20one.%20See%20attached.%20Is%20that%20something%20you%20are%20after%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-10-26%20at%2016.09.07.png%22%20style%3D%22width%3A%20495px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F320036iE032C37EA83E0400%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-26%20at%2016.09.07.png%22%20alt%3D%22Screenshot%202021-10-26%20at%2016.09.07.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2886233%22%20slang%3D%22en-US%22%3ERe%3A%20Top%2C%20middle%20and%20bottom%20averages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2886233%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20fast%20response.%20I%20was%20thinking%20if%20it's%20possible%2C%20with%20code%2C%20to%20sort%20or%20make%20a%20%22normal%20distribution%22%20of%20the%20observations%20of%20each%20area%20and%20thereafter%20make%20a%20lower%2C%20middle%20and%20high%20average%20of%20the%20distribution%20sorted%200-33%25%20(which%20will%20be%20average%20of%20lower)%2C%2033-67%25%20(which%20will%20be%20average%20of%20middle)%2C%2067-100%25%20(which%20will%20be%20average%20of%20higher)%20of%20the%20observations.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20afraid%20that%20your%20way%20of%20doing%20it%20could%20be%20a%20lot%20of%20work%20if%20the%20observations%20get%20to%20many%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20makes%20sense%2C%20I%20don't%20know%20if%20this%20even%20is%20possible%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2886420%22%20slang%3D%22en-US%22%3ERe%3A%20Top%2C%20middle%20and%20bottom%20averages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2886420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1196319%22%20target%3D%22_blank%22%3E%40Benwi123%3C%2FA%3E%26nbsp%3BUnderstand%2C%20but%20statistics%20is%20not%20my%20speciality%20and%20I'm%20not%20really%20up%20to%20speed%20on%20how%20to%20create%20such%20a%20normal%20distribution%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2886536%22%20slang%3D%22en-US%22%3ERe%3A%20Top%2C%20middle%20and%20bottom%20averages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2886536%22%20slang%3D%22en-US%22%3EI%20understand.%20The%20problem%20is%20also%20that%20I%20don't%20want%20to%20determine%20what%20the%20lower%2C%20middle%20and%20higher%20levels%20are%20-%20want%20it%20to%20set%20the%20levels%20according%20to%20the%20inputs%20if%20that%20would%20be%20possible%3CBR%20%2F%3E%3CBR%20%2F%3EAnyways%2C%20thank%20you%20for%20your%20time%3C%2FLINGO-BODY%3E
New 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