How to return a number based on ranking?

[excelos]

Hello

I have three formulas that calculate three different numbers in the format: "23 USA orders", "53 EU orders", "36 ASIA orders".

How can I return the first biggest and second biggest from the above?

Thanks!

 

[Legacy 436357]

Hi if your data is in column B this may work:

=MAX(IFERROR(LEFT(B:B,3)*1,0))

Press CTRL+SHIFT+ENTER to make it an array.

I used a 3 in case the orders go to 100-999

 

[Legacy 436357]

Hi again,

It is better to separate the data into columns using Text to Columns and choose deliminated and check off by spaces. Then you can get the largest and 2nd, 3rd, 4th up to however many you want.

Sheet1

	B	C	D	E	F
23 USA orders	USA	orders
53 EU orders	EU	orders
36 ASIA orders	ASIA	orders

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:157.6px;"><col style="width:65.6px;"><col style="width:48px;"><col style="width:66.4px;"><col style="width:80.8px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]23[/TD]

[TD="align: right"]53[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]53[/TD]

[TD="align: right"]36[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]36[/TD]

[TD="align: right"]23[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F2 =IFERROR(LARGE($C$2:$C$20,ROW(1:1)),"") F3 =IFERROR(LARGE($C$2:$C$20,ROW(2:2)),"") F4 =IFERROR(LARGE($C$2:$C$20,ROW(3:3)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[excelos]

None of these solutions answers my question.

I suppose I need to use the LARGE function as I want to return the first and second largest.

However, the data I want to sort and then return, are a mixture of numbers+text.

 

[Legacy 436357]

If you look at my 2nd reply in column F of the example it shows the largest and then the 2nd and 3rd (which is additional to show how it works as you copy the formula down.