MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### how to select top ten values

• Follow

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

pid
```
 0

```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
```
 0

```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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"vicky" <vimalbarlota@gmail.com> wrote in message
> 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

```
 0

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

"vicky" <vimalbarlota@gmail.com> wrote in message
>i have a data from which i have to select top ten values ..
> the data is something like this
>
> pid
>

```
 0

```hey i need a formula for this
```
 0

```On Feb 3, 1:11=A0pm, vicky <vimalbarl...@gmail.com> wrote:
> hey i need a formula for this

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=3DRank
C2=3DRANK(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
```
 0

```On Feb 3, 1:11=A0pm, vicky <vimalbarl...@gmail.com> wrote:
> hey i need a formula for this

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 =3DRANK(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 {=3DLARGE(IF(\$A\$2:\$A\$10=3D1,\$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
```
 0

6 Replies
673 Views

Similiar Articles:

7/23/2012 12:50:01 PM