how to select top ten values

  • Thread starter Thread starter vicky
  • Start date Start date
V

vicky

i have a data from which i have to select top ten values ..
the data is something like this

pid
 
pid sales
1 5
2 10
3 15
3 10
3 5
1 6
1 7
2 20
2 5
.........

output of only top ten sales (rest of the values i need not dispaly)

pid sales pid sales pid sales
1 7 2 20 3 15
1 6 2 10 3 10
1 5 2 5 3 5
 
If your PID values are in A2:A100 and the sales in B2:B100, the ARRAY
formula (commit with CTRL+SHIFT+ENTER) below will give you the larges B
value whose corresponding pid is 1
=LARGE(IF(A2:A10=1,B2:B10,0),1)

If D2:D11 have the value 1, in E2 enter
=LARGE(IF($A$2:$A$100=D2,$B$2:$B$100,0),ROW(A1))
Copy it down the column to get first, second, third, etc largest
With 2 in F2:F11, copy the formula to G2 and then drag down the column.....
best wishes
 
Hello Vicky,

On my Excel 2007 I select: Home then: Sort and Largest to smallest and voila
you can pick the top ten.


Best wishes

Gabor
 
hey i need a formula for this
Click to expand...

You can use Rank formula for this. Supposing you have Pid in Column A
and Sales in Column B , then in column C you can enter C1=Rank
C2=RANK(B2,$B$2:$B$100,0) Now drag this formula in all the cells and
you will get the rank of number. Apply filter for rank 1 to 10.

Alternatively you can apply a pivot table and use filter to show top
10 values only.

Anand
9910548139
 
hey i need a formula for this
Click to expand...

There can be one more manual process for this if you need to have top
sales for all different periods. Apply custom sort on Pid column and
choose smallest to largest.
Apply filter on columns and choose 1 in pid column. In column C use
the formula =RANK(B2,$B$2:$B$10,0) assuming that last value of 1 in
pid column is shown in A10. Then select 2 in column A for Pid(period)
and apply the same formula in visible cells. Repeat this until you
have done the same with all periods. Now remove filter from Pid column
and apply filter on Rank column , check values from 1 to 10 and all
top sales in periods 1 to 4 would be visible.

Hope that helps,
Anand
9910548139

@Bernard Liengme

Dear Sir,

I tested your formula on following data set

pid sales Result
1 5 10
1 10 10
1 7 10
2 10 10
2 5 10
2 20 10
3 15 10
3 6 10
3 5 10


The third column (C) has the formula {=LARGE(IF($A$2:$A$10=1,$B$2:$B
$10,0),1)} (array entered as per instructions), somehow the results
were not what they should be. I've tried to see the logic behind the
formula through formula auditing but can't figure out where I went
wrong. I was wondering if you could illuminate.

thanks,
Anand
9910548139
 
Back
Top