Conditional ranking

Hello,

I want to rank a spread sheet based on the values of a column, but only if the next column is blank, if it is not blank then use a value in another column but this is including all of the values from the next column and not excluding the value from the initial column.

i am trying

=IF(C2="",RANK(B2,B$2:B$10,1),RANK(D2,(B$2:B$10,D$2:D$10),1))

It is ranking them by all the numbers in the spread sheet, as you can see in the sample two are ranked as 1 and the rank goes up to 18 not 9.

Thanks in advance for any help and please ask any questions if i haven't explained myself fully.

Surn

Hi,

Thanks for the replies. Micope21 that didnt work but thanks for trying. I want the results ranked 1-9 or 9-1 (doesnt really matter) it will do this for the first column if none of cheat are filled, when i fill cheat i want to replace the data from score 1 with score 2, I have attached a new sample with how it should rank. The problem is i only want to include the data from score 2 if cheat is filled in. The inclusion of data must be conditional on the cheat column being filled.

Hope this makes it clearer

Surn

Formula

Please Login or Register  to view this content.

Welcome Glenn Kennedy . I just wanted to avoid helper column.

Thanks for the help guys, I have managed to get it working for two columns but i want to stack it up for 4 and now it just says true or false.
Both the methods are great but my understanding of the format is not good enough. I would like it to say void if all 4 cheat columns are filled in.
I have attached an example based on the original one.

Thanks in advance

Surn

I am trying

=SUMPRODUCT(--(([score 1]*([cheat]="")+[[score 2 ]]*([cheat]<>"")+[score 3]*([cheat]<>"")*([cheat2]<>""))<IF([@cheat]="",[@[score 1]],IF([@cheat2]="",[@[score 2 ]],[@[score 3]] ))))+1

for 3 columns but now it is ranking it up to 10