Large() with twist

[Guest]

Hello,
I am interested in returning the name(s) of a product if it is one of the
top 5 money makers for the year. I believe the large() can return me the top
5 products but 1) what if two or more products have the same value (for
example see below how the top money maker is 500 but it is for both Product A
and Product C) and 2) how do I return both the names of the products that
meet this condition? In summary, so even though I want the Top 5 products,
there could be more than 5 products because some products have the same value
but I want to list out all the products that meet these conditions.
Please help

Thank you,
Dan

Ex.

Product $

Product A 500
Prodcut B 100
Product C 500
Product D 400
Product E 300
Product F 200
Product G 100
Product H 100
....

 

[Guest]

I would either use autofilter and the top 10 (when you select that option you
can
select top 5 or 3 or whatever) or use the RANK function in a help column to
tag each product, then sort by the rank order, default RANK order will rank
highest as number 1 and ties will get the same number


Regards,

Peo Sjoblom

 

[Aladin Akyurek]

You can use (1) pivot tables, (2) AutoFilter (see Peo's reply), and (3)
Advanced Filter with computed criteria to obtain a Top 5 list. What
follows is a formula system...

Let A3:B11 house the sample you provided (with 2nd dollar value changed
for purposes of exposition) including the labels...

{"Product","$";
"Product-A",500;
"Prodcut-B",200;
"Product-C",500;
"Product-D",400;
"Product-E",300;
"Product-F",200;
"Product-G",100;
"Product-H",100}

In C4 enter & copy down:

=RANK(B4,$B$4:$B$11)+COUNTIF(B4:$B$4,B4)-1

In E1 enter: 5

which is the desired Top N value.

E2:

=MAX(IF(INDEX(B4:B11,MATCH(E1,C4:C11,0))=B4:B11,C4:C11))-E1

which must be confirmed with control+shift+enter instead of with the
usual enter.

This formula calculates the number of ties that the Nth (in this case,
5th) dollar value/score might have.

In E4 enter & copy down:

=IF(ROW()-ROW($E$4)+1<=$E$1+$E$2,INDEX($A$4:$A$11,MATCH(ROW()-ROW($E$4)+1,$C$4:$C$11,0)),"")

The foregoing formula creates the top 5 list of products, where 5 is
dynamically adjusted in case of ties of the last 5th value.

In F4 enter & copy down:

=IF(E4<>"",INDEX($B$4:$B$11,MATCH(ROW()-ROW(F$4)+1,$C$4:$C$11,0)),"")

Lists the associated dollar values/scores.