Removing the value error

[dartanion]

I have a row of data which I have sorted into ranks 1 to 33. The formula
works well - =RANK(B4;$B$4:$AI$4;1) but when the row B4 to AI4 has a blank,
then row B5:AI5 which will have the numbers 1 to 33 leaves #VALUE! errors,
and I want blanks. I have tried
=IF(ISERROR(RANK(B4;$B$4:$AI$4;1);"";(RANK(B4;$B$4:$AI$4;1)))) -but get an
error with the;""; highlighted. I have tried ;0; and ;-; but get the error
message all the time.
Any suggestions?

 

[Stephen]

I haven't tried it, but I suspect your brackets don't match properly. Try
this:
=IF(ISERROR(RANK(B4;$B$4:$AI$4;1));"";(RANK(B4;$B$4:$AI$4;1)))

 

[Dave F]

Make an array formula:

=IF(ISERROR(RANK(A4:AI4,A4:AI4)),"",RANK(A4:AI4,A4:AI4))

Hit CTRL + SHIFT + ENTER.

Post back with questions if you don't know how to get Excel to create
an array formula.

Dave

 

[dartanion]

Thanks Stephen- just spent 2 hours changing things and never tried the
brackets!!

 

[dartanion]

Thanks Dave, the answer from Stephen fit the bill.