Can't hide N/A and get RANK to work

[kidb]

Hi all

I'm working on a horse tournament excel sheet for a riding club. Sometimes there are 5 competitors, other times 20. I'm almost finished but can't reach the finish line. The problem is to disable/hide the #N/A error because i can't get RANK formula to work if there is #N/A in it.

Column I has RANK formula =RANK(H4;$H$4:$H$30;1)
Column H has vlookup formula and if there is an #N/A somewhere from H4:H30 the RANK formula doesn't work.



Thanks
Kristján

[brokenbiscuits]

In column H, put in some error handling:

=iferror(vlookup(.....),"")

That way column H will return a blank instead of an #N/A error.

[kidb]

What a quick answer..

I tried this but when I hit enter on my keyboard the ,"" is highligted and i don't know what to do.

[brokenbiscuits]

You're using Excel 2007, yes? If so, this should definitely work.

It could be you have too many parentheses in there? Using your original formula, simply type IFERROR( at the start and then ,"") at the end.
Hopefully that should work?

[zbor]

Or you use ; as a delimiter

=iferror(vlookup(.....); "")

[kidb]

It doesn't work for me, maybe there is something else wrong. I am using Excel 2007.

The formula is like this now:
=IFERROR(VLOOKUP($E$4:$E$30;Stigagjof!$A$2:$B$50;2;2),"")

the ,"" gets highlighted when i hit enter. I tried to use the iferror on few other cells with formulas but with same issue. Could it be related to some settings ?

[Marcol]

I have updated your workbook as BB suggests. Look at all the formulae.

[daddylonglegs]

If you want you can leave the #N/As and rank by using COUNTIF function, i.e. in I4 copied down

=IF(ISNA(H4);"";COUNTIF(H$4:H$30;"<"&H4)+1)

[kidb]

Nice,

; was the problem. It's all magic now.

Thanks alot for mega quick help.