Get 100 first records by criterium formula

[JBlack]

Hello!


I'm struggling with the following problem.
I have a long list of data like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Telephone[/TD]
[TD]No. Customers[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Emily[/TD]
[TD]City[/TD]
[TD]1234567890[/TD]
[TD]778[/TD]
[TD]12,123[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Andrew[/TD]
[TD]City[/TD]
[TD]1234567889[/TD]
[TD]6[/TD]
[TD]678[/TD]
[/TR]
</tbody>[/TABLE]



What I want to do is to get 100 people (Name, City, Telephone), whose ration of Sales to Customers is the biggest.
In this case it would be Andrew.

 

[MajaZa]

The easiest way would be to create another two columns, one in which you store the ratio(call it e.g. ratio) and the other one for ranking (e.g. rank).
Then follow these steps:
1. In the column ratio calculate the ratio. Apply it to all rows.
2. In the column rank paste this formula:
(Suppose your ratio is in the 7th column and starts from the second row)

=RANK(G2,$G$2:$G$[last_row_number])+COUNTIF($G$2:G2,G2)-1

Apply to all your rows.
3. Now, in the place, where you want to have the results the following and drag it for 100 rows.

• In the name column paste this:

=INDEX($B$2:$B$[last_row_number],MATCH(ROW(1:1),$G$2:$G$[last_row_number],0))

• In the city column paste this:

=INDEX($C$2:$C$[last_row_number],MATCH(ROW(1:1),$G$2:$G$[last_row_number],0))

• And in the telephone column this:

=INDEX($D$2:$D$[last_row_number],MATCH(ROW(1:1),$G$2:$G$[last_row_number],0))

Hope that helps!