Removing the value error

  • Thread starter Thread starter dartanion
  • Start date Start date
D

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?
 
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)))
 
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
 
Back
Top