 # Rank two columns?

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(???

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

Rank Two Columns 09-05-2016 2.ods

email -----

If the answer solves your question please tick the .