multiple criteria conditional ranking and protecting multiple sheets in one go

[samktlim]

hi all,
i have been having sleepless nights trying to rank a list of numbers with multiple criteria but in vain...
help is much appreciated.

column a column b column c

pass 9 3
fail 7 8
fail 5 7
pass 5 1
pass 10 5
pass 7 2
fail 4 6
pass 9 3

i would rank column b with the result shown in column c; ranking those with a pass in column a followed by those with a fail in column a in ascending order
....also i am using excel 2003, is it possible to protect multiple sheet in one go?

[benishiryo]

welcome to the forum, samtklim. 1 way is this array formula in C1 assuming data in A1:B8
=SUM(--(IF(A1="fail",B1+1000,B1)>IF($A$1:$A$8="fail",$B$1:$B$8+1000,$B$1:$B$8)))+1

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[samktlim]

hi benishiryo,
having solved my initial problem ranking with multiple criteria,
now i am faced with another..what is in case of ties (eg below is part of my data)
i would want them be be ranked based on another range of values in column h,
making the tie at 4-4 becoming 4-5 and 15-15 to be 16-15.
the formula i used in column g is
=SUM(--(IF(E37="GAGAL",F37+1000,F37)>IF($E$37:$E$67="GAGAL",$F$37:$F$67+1000,$F$37:$F$67)))+1
also if the above formula is to be used in descending order, how should it be edited....thanking you in advance....

attached a sample file for your reference...tq