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?

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)))

"dartanion" wrote in message
...
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?

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

On Dec 17, 10:34 am, dartanion
wrote:
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?

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

"Stephen" wrote:

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)))

"dartanion" wrote in message
...
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?

Thanks Dave, the answer from Stephen fit the bill.

"Dave F" wrote:

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

On Dec 17, 10:34 am, dartanion
wrote:
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?