Challenge! Find top 10 values with a specific criteria

[charnl01]

HI Everyone,

Hopefully someone can help me out. I'm really struggling with the following problem:

1) Find the top 10 models which have the highest "Turns" in the month JUL. (From data tab sheet but projected on Top 10 sheet column F)

Im struggling to find a formula which find the largest value with a specific criteria. Criteria --> Largest, turns in the month JUL

2) Doubles when looking up top 10 models (Top 10 sheet)

In my previous attempts I found a sub optimal solution but I noticed as I moved down the TOP 10 list doubles appear with the same models name because the nr of turns in JUL is the same.

Any input would be much appreciated!

In case of any questions, please let me know.


Many thanks,
Andrew

[Debraj Roy]

by using, two Software, imagination power V2, mind reader (beta)

I came up with this solution..
In top 10 sheet, E29, use formula as..

Please Login or Register  to view this content.

by the way .. both above software are still in testing phase.. Please Test.. so that I can launch in market..

[AB33]

Pivot Table will give a good start. Filter the top 10 by any criteria.

[Ron Coderre]

Using your posted workbook...
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) begins the list of highest Turns for the referenced month

Please Login or Register  to view this content.

Copy F29 and paste into F30:F38

This REGULAR formula displays the MODEL for each of those highest turns

Please Login or Register  to view this content.

Copy that formula down through D38

With your sample data, these are the results:

Please Login or Register  to view this content.

Is that something you can work with?

[charnl01]

Thanks for the help! This is exactly what I'm looking for

I just had one additional question. I've now extended the list to include all models so now you have a Top 13.

With the help of your formula's I derive at the following sample data.

1 AVM 6360 4.04
2 CV-5000 2.87
3 8685DVB 2.75
4 CV-6000 1.96
5 Converter 1.92
6 CI+ CAM 1.91
7 CV-7000 1.48
8 8485DVB 1.33
9 CV-5000-A 1.15
10 CV-8000 0.18
11 AVM 7170 0.01
12 #NUM! #NUM!
13 #NUM! #NUM!

Because model 12 and 13 dont have a number it shows up as #NUM. But I would still like to list the 12th and 13th model type

Do you have an idea how I can still so the 12 and 13th model?

[Ron Coderre]

The quick fix would be to replace the dashes (-) with zeros. The alternative would be less elegant formulas.
Also...since all of the 0's would be tied...the formulas would get really ugly when trying to list them.
Perhaps another approach would be appropriate:
• List all of the MODELS
• Calculate their respective Turns for the selected month
• Use a rank formula that increments ties
• List the ranked MODELS

I could get to that a bit later, there is talent on this forum that could also help.

[charnl01]

many thanks for te reply!

I've managed to implement your formula's! There awesome

Next challenge would be to list the models which has tied nrs.


I will open up a new thread to check which talent can answer my question

Many thanks for your help