Ask Your Question

Rank two columns ?

asked 2016-09-06 02:28:50 +0200

JG101 gravatar image

updated 2016-09-06 04:44:07 +0200

mark_t gravatar image

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


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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-09-06 03:44:05 +0200

mark_t gravatar image

updated 2016-09-06 04:54:14 +0200

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:


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

edit flag offensive delete link more


email -----

JG101 gravatar imageJG101 ( 2016-09-06 04:24:40 +0200 )edit

I added your attachment to your question. I'll modify and attach to answer.

mark_t gravatar imagemark_t ( 2016-09-06 04:45:33 +0200 )edit

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?

JG101 gravatar imageJG101 ( 2016-09-06 05:11:30 +0200 )edit

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.

mark_t gravatar imagemark_t ( 2016-09-06 05:27:46 +0200 )edit

Awesome, thank you Mark for the Correction. my apologies

JG101 gravatar imageJG101 ( 2016-09-06 05:57:55 +0200 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2016-09-06 16:04:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-06 02:28:50 +0200

Seen: 338 times

Last updated: Sep 06 '16