3 Highest Values

[Guest]

I am trying to link the three highest values from a series to another sheet.
For instance, I have a list of approx. 50 different calculated values. I want
to track the three highest on a seperate worksheet. I also need to link the
labels of those three values.

 

[Bob Phillips]

=LARGE(Sheet1!A1:A100,1)

then ,2 and ,3?

Which labels?

 

[scottymelloty]

how about use the Rank Function which will make the top three 1,2,3 the
link whatever you need to the 1,2,3 in the rank colum

 

[Guest]

A little background:

The workbook is tracking the amount of scrap parts we make during
manufacturing. I am tracking PPM, or "Parts per Million" defective. So, let's
say I have part number 250, at the drill operation and it has a PPM of 50. I
want to be able to track the three highest PPM from my summary sheet. I also
want to know what part number and operation it relates to.

 

[scottymelloty]

Ok ive got a solution that work

Column A1 - Put in a header name ie "PART NUMBER"
Column B1 - Put in a header name ie "PPM"
Column C1 - Put a header name "RANK"

In Column C2 put this formula =RANK(B2,$B$2:$B$1000,0) copy thi
formula down all the c column to the last data enetered in column A an
B ie if you have 20 lines of data copy formula so its next to each ro
of data

In D2 Put this formula =IF(C2=1,"3 HIGHEST",IF(C2=2,"
HIGHEST",IF(C2=3,"HIGHEST",0))) , again copy this down the whole Colum
D to be in line with all other column data

As you should see next to the top 3 PPM in column D is should say
HIGHEST in same row, Highlight the D1 Column which should be blank
Click on DATA - FILTER - AUTO FILTER, this should bring an arrow up o
all the heading, if you click the arrow on the D1 it will bring up
list, at the bottom it will be "3 HIGHEST" , click this and it wil
bring up the 3 highest PPM's, if you want to get it all back, lcik th
arrow again and select all.


You can always hide the Rank Column if you dont want to see that.
Hope this helps, let me know how u get on

 

[Guest]

Thanks guys. I'm going to mess with it and see if I can get it do what I need
to. Appreciate it.

 

[Aladin Akyurek]

Let A3:B10 house the following data...

{"Part#","PPM";
250,50;
270,50;
280,45;
290,35;
290,45;
310,27;
320,20}

In C3 enter: Rank

In C4 enter & copy down:

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

In D1 enter: 3 [ which means Top 3 ]

In D2 enter:

=COUNTIF(B4:B10,LARGE(B4:B10,D1))-1

In D4 enter & copy down:

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

If so desired...

In E4 enter & copy down:

=IF(D4<>"",INDEX($B$4:$B$10,MATCH(ROW()-ROW($D$4)+1,$C$4:$C$10,0)),"")

The result area in D:E will look like this:

{"Top Part#","PPM";250,50;270,50;280,45;290,45}

Note. The foregoing system differs with regard to the formula in D2 from th
formula in the base system I published at various occasions, e.g.:

http://216.92.17.166/board2/viewtopic.php?t=69970