formula to give me top 10

How about "Rank" ...
for example lets say you percentages are in ColB starting in Row 1 then in C1 place this formula and then copy down column.

=RANK(B1,B:B)

Once formula in place sort by column C.




QUOTE FROM HELP FILE :

Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)

Syntax

RANK(number,ref,order)

Number is the number whose rank you want to find.

Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

Order is a number specifying how to rank number.

If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.


If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

Remarks

RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

Examples

If A1:A5 contain the numbers 7, 3.5, 3.5, 1, and 2, respectively, then:

RANK(A2,A1:A5,1) equals 3
RANK(A1,A1:A5,1) equals 5

The formulas are...

C2:

=RANK(B2,$B$2:$B$9)+COUNTIF($B$2:B2,B2)-1

E2:

=INDEX($A$2:$A$9,MATCH(ROW(1:1),$C$2:$C$9,0))

F2:

=INDEX($B$2:$B$9,MATCH(ROW(1:1),$C$2:$C$9,0))

Thanks once again Nimrod and Aladin,
As always help is just a few clicks away. Another problem solved.
Peter

peter1238 said:

Thanks once again Nimrod and Aladin,
As always help is just a few clicks away. Another problem solved.
Peter

Click to expand...

What about ties?

You could use a Pivot Table, which has a Top/Bottom ten feature built in (or Top 5 etc. - whatever you specify). With Names in the row area, and % in the Data area, set % to show the top ten.

Better still, why don't you just sort your range on column B, descending. All the salespeople will be shown ranked top to bottom. Just copy and paste the top 10 rows to where-ever.


Regards,

Mike

Hi this is exactly what I am after but with a slight difference I run entertainment at our local club and we run a audience rating on the music acts we have on and they are voted by the percentage of how much of the audience want to see them but our ratings go to 2 decimal points and when I put the formulas that aladin-akyurek has kindly given us I seem to get different percentages equally getting a certain place how can I get the formula to differentiate different percentage values eg 92.89 being higher than 92.87

how about sumproduct for producing the ranks:
assuming that you have the percentages in column C2:C100
=sumproduct(--(C2>=C$2$:C$100$)) in a helper column
this will surely include digits
you just have to copy it down to the last data

then you can go with index+match for 1 to 10 to get the names of the top 10

HolyExcel said:

how about sumproduct for producing the ranks:
assuming that you have the percentages in column C2:C100
=sumproduct(--(C2>=C$2$:C$100$)) in a helper column
this will surely include digits
you just have to copy it down to the last data

then you can go with index+match for 1 to 10 to get the names of the top 10

Click to expand...

Thank you for your reply HolyExcel please forgive my lack of Excel knowledge but where exactly would I place this formula would it be in the coloumn I have all the percentages in in my case column B and if so where?

Hi,

no, you would place it in the column beneath it if free or any other column if your reference is fine. So if your percentages are all in column B then insert them in any other column like this:

=sumproduct(--($B2>=$B2:$B100000))
If column C is empty, please do it there...if not, then the next free column
Please note: as I do not know how many rows of data you have, I have entered in BOLD in above formula. If you have more than 100000 rows you have to adjust this.

Also you will have to copy this formula to the last row of your data in the column where you have entered it.

Hope it works and helps