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

[Luu4466]

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.

[Ochimus]

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

[Luu4466]

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?

[Glenn Kennedy]

You say:

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

average of what... exactly?

[Glenn Kennedy]

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???

[Luu4466]

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

[Luu4466]

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.

[Glenn Kennedy]

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

[Glenn Kennedy]

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

[Luu4466]

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.

[Luu4466]

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.

[Glenn Kennedy]

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???

[Luu4466]

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.

[Glenn Kennedy]

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.

[Glenn Kennedy]

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

[Luu4466]

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

[Glenn Kennedy]

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

[Luu4466]

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?

[AliGW]

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

[Glenn Kennedy]

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!!

[Glenn Kennedy]

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.

[Luu4466]

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

[Luu4466]

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!

[Glenn Kennedy]

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!!).

[Luu4466]

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

[Luu4466]

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.

[Glenn Kennedy]

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.

[Glenn Kennedy]

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

[Luu4466]

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.

[Glenn Kennedy]

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...

[Ochimus]

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

[Luu4466]

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.

[Ochimus]

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

[Luu4466]

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.

[Ochimus]

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

[AliGW]

@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.

[Luu4466]

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.

[Luu4466]

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.

[Ochimus]

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

[Luu4466]

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

[AliGW]

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.

[Ochimus]

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

[p45cal]

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

[Ochimus]

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

[Luu4466]

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

Excellent. I was looking for this. Thank you p45cal!

[Luu4466]

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!