+ Reply to Thread
Results 1 to 46 of 46

How to find the average top 5 values and average bottom 5 values + index

  1. #1
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    How to find the average top 5 values and average bottom 5 values + index

    Hi community.
    I want to find the average top 5 values and average bottom 5 values per category of item per brand and excluding blank values. Then I want to associate data according to the value which has been found.
    I work on the worksheet "UK (2)"
    My report 1 (bottom 5 lowest price) seems to work but the second report is not working (top 5 highest price).
    I would like to find the solution for the second report.
    Thank you for your help.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Luu4466; 12-16-2020 at 07:19 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Cols A and B on attached list thirty dummy products and their prices.

    Cols D and E list the five most expensive products using this formula in E2 and copied down four rows:

    =LARGE(B$2:B$31,ROWS(E$2:E2))

    With the formula in Col D linking that to a Product:

    =INDEX($A$1:$A$31,MATCH($E2,$B$1:$B$31,0))

    Cols G and H list the five with the lowest prices using this formula in H2 copied down four rows:

    =SMALL(B$2:B$31,ROWS(E$2:E2))

    With the formula in Col G linking that to a Product:

    =INDEX($A$1:$A$31,MATCH($H2,$B$1:$B$31,0))

    Hope that helps

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-14-2020 at 09:29 PM.

  3. #3
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Ochimus View Post
    Cols A and B on attached list thirty dummy products and their prices.

    Cols D and E list the five most expensive products using this formula in E2 and copied down four rows:

    =LARGE(B$2:B$31,ROWS(E$2:E2))

    With the formula in Col D linking that to a Product:

    =INDEX($A$1:$A$31,MATCH($E2,$B$1:$B$31,0))

    Cols G and H list the five with the lowest prices using this formula in H2 copied down four rows:

    =SMALL(B$2:B$31,ROWS(E$2:E2))

    With the formula in Col G linking that to a Product:

    =INDEX($A$1:$A$31,MATCH($H2,$B$1:$B$31,0))

    Hope that helps

    Ochimus
    Thank you for your answer. Can you update the formula to avoid blank value?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    You say:

    I want to find the average top 5 values and average bottom 5 values.

    average of what... exactly?
    Glenn



  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    Incidentally, what are you making multiple sheets for this? Would it not be easier (not to mention faster and more efficient) to have 1 raw data sheet and one dashboard sheet, driven by a country selector in a dropdown box???

  6. #6
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    For example UK: I would like an average per brand per category of item.

  7. #7
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Glenn Kennedy View Post
    Incidentally, what are you making multiple sheets for this? Would it not be easier (not to mention faster and more efficient) to have 1 raw data sheet and one dashboard sheet, driven by a country selector in a dropdown box???
    Yeah. It' a great idea. But I don't get the time to move back.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    It would take a LOT less time to do change course instead of updating MULTIPLE country sheets, one at a time!!

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    Somewhere among your squillion sheets do you have a dynamic list of product categories??

  10. #10
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    I totally understand but I can't move back at this time. I need to deliver this today and I can't change "design" of the workbook.

  11. #11
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Glenn Kennedy View Post
    Somewhere among your squillion sheets do you have a dynamic list of product categories??
    I will make it to learn but I can't do it today.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    So you have 12 categories... for each of them you want an average of up to 5 brands, up to 60 rows in total? Times 2 for high and low???

  13. #13
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Glenn Kennedy View Post
    So you have 12 categories... for each of them you want an average of up to 5 brands, up to 60 rows in total? Times 2 for high and low???
    I have 12 categories and 4 brands and I search bottom 5 values and top 5 values without blank values.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    I am still not sure what you want. In my view your latest query is less important than your overall direction of travel. Before you drive yourself into a nervous breakdown, take 5 minutes to look at sheet FRANCE. You will see thta I have created a dd box in A1. Select whatever country you wish...

    It instantly reproduces whatever you had in ALL those other sheets. You are making a NIGHTMARE out of a relatively simple task. I aso note that your Named ranges are NOT dynamic. Add on a few rows and they are OUT OF DATE. You need to step back from this for a moment and think about your direction.

    In my view you are going the wrong way about this. It will be so complicated to maintain this sheet. I strongly advise you work with me to get it RIGHT.

    It took me about 10 mins to set this up. Most of that was adding error statements to some of the formulae.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    The graphs also change to reflect the country chosen in A1..

  16. #16
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Ok. I'm ready to take the good direction. Can you share the file where you put dd box in A1?

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    Named Range Countries:
    ='Raw data'!$A$2:INDEX('Raw data'!$A:$A,MATCH("zzz",'Raw data'!$A:$A))

    will auto-adjust the ranges if new countries on new data rows are added.

    Sheet: Lists, A2:
    =SORT(UNIQUE(Country_GK,FALSE),,1)

    returns a list of unique countries (dynamically).

    New Named range Uniq_Country

    =Lists!$A$2:INDEX(Lists!$A$2:$A$300,SUMPRODUCT(--(LEN(Lists!$A$2:$A$300)>0)))

    will pick up unique country names from the above and populate the DD box with them.

    It looks like I need to create just a few new Named Ranges to make all tehe formulae fully automated. take a look at this for a moment.

    I am glad you decided to change course... a dashboard is meant to be a summary, not a sheet-creation industry!!

    an
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Oh, it's so efficient You are very strong and it's very very nice from you to help me like this.

    Which worksheet have this formula?
    =Lists!$A$2:INDEX(Lists!$A$2:$A$300,SUMPRODUCT(--(LEN(Lists!$A$2:$A$300)>0)))

    And what is the purpose of this formula?

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,653

    Re: How to find the average top 5 values and average bottom 5 values + index

    You've marked the thread as solved - if you want further input, you would be better off removing that tag for the time being.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    Next stage. I made a named Range (End_Price) to pick up the end user price:
    ='Raw data'!$J$2:INDEX('Raw data'!$J:$J,MATCH(1E+100,'Raw data'!$J:$J))

    So, then I changed all the formulae to use their nice shiny new Named ranges (CTRL-H, of course not manually!!), so they now look like this:

    =IFERROR(INDEX('Raw data'!A:A,AGGREGATE(15,6,ROW(Countries)/((Countries=$A$1)*(End_Price=$I4)),COUNTIF($I$4:$I4,$I4))),"")

    So. If:

    more data
    more countries

    are added on the dd and the named ranges will auto-adjust. take a look. Once you're happy with that, we need to look at global (it looks different) and then we can revisit your actual question. Although I do have to write a talk on PowerPoint this morning as well!!
    Attached Files Attached Files

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Luu4466 View Post
    Oh, it's so efficient You are very strong and it's very very nice from you to help me like this.

    Which worksheet have this formula?
    =Lists!$A$2:INDEX(Lists!$A$2:$A$300,SUMPRODUCT(--(LEN(Lists!$A$2:$A$300)>0)))

    And what is the purpose of this formula?
    The formula dynamically selects allof the countries listed in sheet lists column A and uses them to populate the DD box in A1 of National. So, add (for example) Zanzibar at the end of column A of raw data. It will appear at the bottom of Lists column A and will appear in the DD choices in National A1.

  22. #22
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Thank you for everything. I need to take a big look on this. You can make your Powerpoint

  23. #23
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by AliGW View Post
    You've marked the thread as solved - if you want further input, you would be better off removing that tag for the time being.
    Ok, thank you for your recommendation!

  24. #24
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    One last thing for now. Do you really need a set of sheets for gloabal. Can it not simply be an "all" version of the national sheet? If so, we can dump a load more unnecessary stuff from your file.

    I'll be looking in and out here this morning. When you post something, send me a PM as well.

    Off now to think about the sanitary rules for the export of poultry meat from Colombia to Japan and to finish my PowerPoint on topic. (not joking... that's what I'm meant to be doing today!!).

  25. #25
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Good luck with your PPT on the topic with poultry meat.
    I follow your recommendation. We can make an "all" version".

  26. #26
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    To find average national bottom 5, formula is:
    Please Login or Register  to view this content.
    For global, I try to find average global bottom 20:

    Please Login or Register  to view this content.
    I deleted ('Raw data'!$A$2:$A$1201=$B$1) because i don't want country criteria.
    I changed ROWS($3:3) to ROWS($71:71) because I want to establish my report from the row 71.

    But it doesn't work.

  27. #27
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    One step at a time. If you are (now) aiming for a one-size-fits all sheet, there will have to be some compromises. I am taking a break from Colombian chickens... and will take a look. Don't do anything further for 15 mins or so.

  28. #28
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    OK. Here is iteration 4, with more a bug or two ironed out. NOW it is beginning to really look like a dashboard.
    Attached Files Attached Files

  29. #29
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Glenn Kennedy View Post
    OK. Here is iteration 4, with more a bug or two ironed out. NOW it is beginning to really look like a dashboard.
    So smart to create this dropdown "all countries" to make the global analysis.
    I don't want to bother you anymore. It's a big job.

  30. #30
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,793

    Re: How to find the average top 5 values and average bottom 5 values + index

    Don't worry about it. Happy to help. Apart from a little time spent repairing a mistake I made... it has taken me only about 30-45 mins to get it to V4.

    So, play with V4 for a while and then let's get back to your original question in this thread!!!

    I'm going away now for an hour or so. Back later.

    In the meantime, play with it and then re-describe what you really want for averages. It sounds like a lot of "stuff" which may not yield you much information...

  31. #31
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Luu,

    Rather than the five large "single value" bar charts, your users might find Dial charts more "user friendly"?

    You may have to "Enable Macros" and "Enable content" to run it, as the values are set automatically by a small Macro that runs whenever you change either the Country in B2 on the Dashboard, or the raw data for the Categories.

    Formula to set them are in GRAPHS AG - AL which can either be moved to a different sheet, set as "White" font, or covered by another chart?

    Dials show the Average Price for the five Categories you shows originally, with the pointers showing the value in relation to the Maximum prices.

    Antenna dial also shows the Min and Max values at the start and end of the scale, if that is of any value to your audience.

    Hope you find it useful.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-15-2020 at 12:08 PM.

  32. #32
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    It's very user friendly, and I will use it in the future.
    The table located in "AG AL" in the worksheet "Graphs" is almost good. But I want the average per category of item per brand.

    I want the same report as the table A1:I6 (bottom 5 lowest price) AND also for A8:I13. But I have errors for the second report.
    Last edited by Luu4466; 12-16-2020 at 07:13 AM.

  33. #33
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Apologies for forgetting that was the original request in your first post!

    Attached give you the average price from each Supplier for each Category in whichever country you select.

    DASHBOARD sheet L39 - P53 shows the average price for each supplier of all twelve Categories in whichever country you selected in B1 (if they do not sell it there, cell is blank).

    GRAPH sheet data in AG4 - AK9 shows Min, Max and Average prices and Average % for each supplier for whichever product you selected in DASHBOARD sheet B21 in whichever Country you selected in B1.

    That data sets the four Supplier Dials, with the Header identifying the Country and Category being presented.

    Where a supplier does not provide that category for that market, the relevant Dial(s) show(s) 0% and the value is a dash.

    Think that does it?

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-16-2020 at 08:26 AM.

  34. #34
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Ochimus, this is exactly what I want.
    You have created a table in L39:P53 in the workbook GK4. I need to display the bottom 5 lowest values in my report 1 (located in J5:J9) and the top 5 highest values of this table (located in J14:18) and my question can be solved.
    Last edited by Luu4466; 12-16-2020 at 11:27 AM.

  35. #35
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Luu4466,

    Can you explain again exactly what your two lists need to show?

    Your original post asked for the 'average' Top 5 values and 'average' Bottom 5 values per category of item per brand.

    Assuming the Brand is the supplier, my understanding is the two lists should therefore look like the attached mock-up, which shows the five Highest and Lowest values for each Category and their averages for each supplier across for columns.

    But the Low Price list in your Dashboard has two different categories, fortunately from the same supplier, and the Highest Price mixes five Categories from three suppliers?

    Value your clarification, so we can produce what you want to go alongside the overall Average per category per supplier I created in rows 40 - 53.


    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-16-2020 at 09:11 PM.

  36. #36
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,653

    Re: How to find the average top 5 values and average bottom 5 values + index

    @bruno

    Could you please address your responses specifically to the case in hand? Most of the replies you have made to threads so far today are just generic advice that anyone can find via Google.

    Please adapt what you've offered here to the OP's requirements. Thanks.

  37. #37
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Ochimus View Post
    Luu4466,

    Can you explain again exactly what your two lists need to show?

    Your original post asked for the 'average' Top 5 values and 'average' Bottom 5 values per category of item per brand.

    Assuming the Brand is the supplier, my understanding is the two lists should therefore look like the attached mock-up, which shows the five Highest and Lowest values for each Category and their averages for each supplier across for columns.

    But the Low Price list in your Dashboard has two different categories, fortunately from the same supplier, and the Highest Price mixes five Categories from three suppliers?

    Value your clarification, so we can produce what you want to go alongside the overall Average per category per supplier I created in rows 40 - 53.


    Ochimus
    Thank you Ochimus for your answer.
    But I want to keep the design in my post of the beginning.
    I would like on table of report from A1:I6 for the average bottom 5 values. The values are good in the first report.
    I would like on table of report from A8:I13 for the average top 5 values. The values are NOT good in the second report.

  38. #38
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by bruno_johnson View Post
    Average Top Or Bottom 3 Values With Formulas

    In this example, I will calculate the average of largest or smallest 3 numbers, the following formulas may do you a favor.

    To get the average of the largest 3 values, please enter this formula:

    =AVERAGE(LARGE(A2:A20, ROW(1:3))), (A2:A10 is the data range that you want to average, 1:3 indicates the number of the largest values you need, if you want to average the largest 10 values, you just need to change it as 1:10.), and then press Ctrl + Shift + Enter keys together to get the correct result.

    To average the smallest 3 values, you can apply this formula: =AVERAGE(SMALL(A2:A20, ROW(1:3))) (also, if you want to average the smallest 10 values, change 1:3 to 1:10), and remember to press Ctrl + Shift + Enter keys to return the right result.

    To average the numbers excluding the bottom 3 values, please type this formula: =AVERAGE(IF(A2:A20>SMALL(A2:A20,3), A2:A20)) (the number 3 indicates the number of the smallest value, if you want to average ignoring the smallest10 values, you just need to change 3 to 10), and should press Ctrl + Shift + Enter keys after entering this formula.

    Hope this was helpful.
    Jerry.
    Thank you Jerry for your answer.
    But, it's not what I search.
    My first report is working well. I can see the average bottom 5 end user price in EUR after VAT per category of item per brand.

    But the second one is not working. I can't see the average top 5 highest end user price in EUR after VAT per category of item per brand correctly.

  39. #39
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Luu,

    Still need your explanation on this?
    Example: Antanna to Spain.
    Only three suppliers provide them - AB at 213.75, MedEl at 603.14 and OM at 85.00.
    If you want just the Highest, a formula can easily find the relevant row on the Raw Data.
    But you are asking for the average of those prices, which is 255.47, which will not link to any of the records on the Raw Data?

    Ochimus
    Last edited by Ochimus; 12-21-2020 at 01:15 PM.

  40. #40
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Ochimus View Post
    Luu,

    Still need your explanation on this?
    Example: Antanna to Spain.
    Only three suppliers provide them - AB at 213.75, MedEl at 603.14 and OM at 85.00.
    If you want just the Highest, a formula can easily find the relevant row on the Raw Data.
    But you are asking for the average of those prices, which is 255.47, which will not link to any of the records on the Raw Data?

    Ochimus
    For Spain, Antenna:
    OM: 85
    OM: 85
    AB: 213.8
    MeDEl: 603.1
    Cochlear:N/A

    I want the average like this:
    OM: 85
    AB:213.8
    MedEl: 603.1
    Cochlear: N/A

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,653

    Re: How to find the average top 5 values and average bottom 5 values + index

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  42. #42
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Luu,

    I am making one last plea for clarity here.

    Whichever country you select, you have twelve potential categories, each of which potentially has four brands, but a brand can have more than one entry.

    Step 1 is to calculate the "average" price each supplier charges for all the versions of that Category they sell in that market.

    That gives you forty eight potential "average" prices (four suppliers, twelve Categories).

    Step 2 identfies the five Highest Average Prices and posts them in the Table (even if it means more than one price will be on the same Category and even the same supplier providing the same Category).

    The problem - as I tried to explain above - is that the Database may not HAVE a product matching that "Average" price?

    (E.g. Supplier X sells four versions of Antenna in Country Y, ranging from 200 to 600. Average price is 420, That turns out to be one of the five highest "average prices" across all twelve categories, so goes on your List.

    But unless one of their versions matches that 'average' price, you can only identify the Supplier is X and the Category is Antenna, but what do you list as the 'Name of item' and 'Reference No' of the item?)

    Ochimus
    Last edited by Ochimus; 12-22-2020 at 04:38 PM.

  43. #43
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    258

    Re: How to find the average top 5 values and average bottom 5 values + index

    Is the attached the sort of thing you're looking for?

  44. #44
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,177

    Re: How to find the average top 5 values and average bottom 5 values + index

    Luu,

    Attached DASHBOARD sheet has three 'tables'

    Select Country from DropList in B1.

    Rows 3 - 17 show the Average Values of each Category sold by each supplier to that country.

    C21 - C25 lists the HIGHEST FIVE average values from those potential forty eight, ranked by size. B21 - B25 show the Supplier and A21- A25 shows the Category.

    Rows 29 - 33 do the same for the five LOWEST average prices.

    As pointed out previously, because you are reviewing average prices, in the majority of cases there will not be a particular Item that matches the price, so you cannot narrow the list down beyond the Supplier and Category.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-23-2020 at 03:19 PM.

  45. #45
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by p45cal View Post
    Is the attached the sort of thing you're looking for?
    Excellent. I was looking for this. Thank you p45cal!

  46. #46
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    85

    Re: How to find the average top 5 values and average bottom 5 values + index

    Quote Originally Posted by Ochimus View Post
    Luu,

    Attached DASHBOARD sheet has three 'tables'

    Select Country from DropList in B1.

    Rows 3 - 17 show the Average Values of each Category sold by each supplier to that country.

    C21 - C25 lists the HIGHEST FIVE average values from those potential forty eight, ranked by size. B21 - B25 show the Supplier and A21- A25 shows the Category.

    Rows 29 - 33 do the same for the five LOWEST average prices.

    As pointed out previously, because you are reviewing average prices, in the majority of cases there will not be a particular Item that matches the price, so you cannot narrow the list down beyond the Supplier and Category.

    Ochimus
    Excellent also. Your proposal is really good also. Thank you a lot Ochimus!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Take average of a range -- excluding top 1/3 and bottom 1/3 and zero values
    By saravnepali in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-01-2020, 06:29 PM
  2. Average unique values from an index
    By Davidendum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2019, 04:31 AM
  3. [SOLVED] How to Average values of Top/bottom 3 of another column
    By TK2013 in forum Excel General
    Replies: 7
    Last Post: 07-14-2019, 08:33 AM
  4. Average the bottom 95$ of values
    By shauncie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2016, 03:27 PM
  5. Calculating the average(s) of top, middle and bottom quarter values
    By AndrejLav in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2015, 10:30 PM
  6. Replies: 13
    Last Post: 10-08-2014, 08:35 AM
  7. drop top and bottom values before computing average
    By jimboryan in forum Excel General
    Replies: 2
    Last Post: 07-15-2014, 04:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1