Get 100 first records by criterium formula

JBlack

New Member
Hello!

I'm struggling with the following problem.
I have a long list of data like this:
 ID Name City Telephone No. Customers Sales 1 Emily City 1234567890 778 12,123 2 Andrew City 1234567889 6 678

<tbody>
</tbody>

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.

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

MajaZa

New Member
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).
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)
Code:
``=RANK(G2,\$G\$2:\$G\$[last_row_number])+COUNTIF(\$G\$2:G2,G2)-1``
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:
Code:
``=INDEX(\$B\$2:\$B\$[last_row_number],MATCH(ROW(1:1),\$G\$2:\$G\$[last_row_number],0))``

• In the city column paste this:
Code:
``=INDEX(\$C\$2:\$C\$[last_row_number],MATCH(ROW(1:1),\$G\$2:\$G\$[last_row_number],0))``

• And in the telephone column this:
Code:
``=INDEX(\$D\$2:\$D\$[last_row_number],MATCH(ROW(1:1),\$G\$2:\$G\$[last_row_number],0))``

Hope that helps!

Replies
1
Views
215
Replies
1
Views
322
Replies
3
Views
309
Replies
2
Views
288
Replies
2
Views
338

1,191,386
Messages
5,986,315
Members
440,017
Latest member
vasanrajeswaran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back