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
should read by Rank
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
Edit to add updated example (based on attachment added to question)
Rank Two Columns 09-05-2016 2.ods
email -----
I added your attachment to your question. I’ll modify and attach to answer.
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
If the answer solves your question please tick the 
.