How to return a number based on ranking?

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
L

Legacy 436357

Guest
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
 
Last edited by a moderator:
L

Legacy 436357

Guest
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


BCDEF
223 USA orders23USAorders53
353 EU orders53EUorders36
436 ASIA orders36ASIAorders23

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited by a moderator:

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
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.
 
L

Legacy 436357

Guest
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,420
Messages
5,528,668
Members
409,829
Latest member
CFreeamaz

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top