# Rank two columns ? [closed] see attached document Rank Two Columns 09-05-2016 2.ods

Rank Column B and Column E Lowest number equals = 1 , Highest number equals = 32 or highest number available given duplicate OA numbers

example:

Row 5 Column B 1 Column E 5

Row 6 Column B 10 Column E 9

I would like to Rank two columns

Rank 1 Row 5 Column B

Rank 2 Row 5 Column E

Rank 3 Row 6 Column E

Rank 4 Row 6 Column B

thank you in advance for any help provided.

How do I write, =ifna(rank(???? or =iferror(rank(?????

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-11 19:27:55.769430

Sort by » oldest newest most voted The RANK function will return the rank position of the value passed to the function within the data range.

You can specify a range consisting of two non adjacent columns using the tilde '~' character.

For example as you described:

=RANK(9,B5:B6~E5:E6,1)


Would give the value 3, as 9 is third rank in the data.

If you search for a value that does not exist in your data then the rank function will return the value -1. If you want to detect invalid data and return some other value or string then you can use the IF function. If you try to search for a non-numeric value this will give the #VALUE error which you can detect with IFERROR.

=IFERROR(IF(RANK(A11,B5:B6~E5:E6,1)>0,RANK(A11,B5:B6~E5:E6,1),"Data not found"),"Data is not a number")


Attached example, Untitled 19.ods

Edit to add updated example (based on attachment added to question) Rank Two Columns 09-05-2016 2.ods

more

thank you for reviewing

what happens when the numbers are the same, should the Ranking be the same?

Row 16 Column B and Column E both equal 22, but different Ranking, should they both have the same Ranking since the numbers match?

Row 16 B and E are not the same value, the number format is not showing decimal places to show the difference. If they had the same value they would have the same rank, example B21, B33 and E19, all have rank 23 and then the next available rank is 26.

Awesome, thank you Mark for the Correction. my apologies