Thread: #VALUE! Appearing on Cell

I have this formula on a cell. It works if data on H3 is populated, otherwise, I get #Value!. I tried using ISERROR but it didn't work. I can't seem to figure out where the hold up is.

Can you help me?

Thanks in advance!

=IF((RANK(H3,$H$2:$H$4,0))=1," ",IF((RANK(H3,$H$2:$H$4,0))=2," ",IF((RANK(H3,$H$2:$H$4,0))=3,"Winner!"," ")))

See attached file too. I'm using 2003 version. Again, many thanks!

First thing to aware of ... a Space " " is not the same as a Null ""

The #VALUE! error results from the fact you can't apply RANK to Text values - your source cell is populated with Text (presently a Space but in reality it would be better were it a Null).

I would suggest (based on existing logic) you revise I2 to

=IF(COUNT($H$2:$H$4)<3,"",REPT("Winner",RANK($H2,$H$2:$H$4)=3))

or if less than 3 scores is ok reverse the RANK logic

=IF(ISNUMBER($H2),REPT("Winner",RANK($H2,$H$2:$H$4,1)=1),"")