# count problem

This is a discussion on count problem within the Excel Questions forums, part of the Question Forums category; Hi I have the following formula: =RANK(F4,F3:F102,0) which works so long as the range F3:F102 has a number. if it ...

1. Hi
I have the following formula:
=RANK(F4,F3:F102,0)
which works so long as the range F3:F102 has a number. if it doen't it returns #Value. How can i get it to return "0" if there is nothing in the range F3:F102?
Thanks
mac

2. Try the following:
=IF(ISBLANK(F4),0,RANK(F4,F3:F102))

_________________
Kind regards,

ALCHARA
[ This Message was edited by: Al Chara on 2002-08-09 09:02 ]

[ This Message was edited by: Al Chara on 2002-08-09 09:04 ]

3. Al
That doesn't work, i still get the #Value!
i forgot to say that theris a link in the other cell, so technically it isn't blank.

[ This Message was edited by: Macker72 on 2002-08-09 09:08 ]

4. On 2002-08-09 08:50, Macker72 wrote:
Hi
I have the following formula:
=RANK(F4,F3:F102,0)
which works so long as the range F3:F102 has a number. if it doen't it returns #Value. How can i get it to return "0" if there is nothing in the range F3:F102?
Thanks
mac
=IF(COUNT(F3:F102),RANK(F4,F3:F102),"")

Should F4 not be F3 and the range locked: \$F\$3:\$F\$102?

You can omit 0 as the 3rd arg of RANK if you want.

this works:
=IF(COUNT(F4),RANK(F4,\$F\$3:\$F\$102),0)

Thanks
Mac

[ This Message was edited by: macker72 on 2002-08-09 09:23 ]

6. On 2002-08-09 09:15, Macker72 wrote:
i had removed the \$ and used the second cell in my list so this is OK.
I have used your formula and i still get #value, is it because F4 contains a link to another workbook?
Try:

=IF(LEN(F4),IF(COUNT(Range),RANK(F4,Range),""),"")

You probably have "" in F4.

7. On 2002-08-09 09:15, Macker72 wrote:
this works:
=IF(COUNT(F4),RANK(F4,\$F\$3:\$F\$102),0)

Thanks
Mac

[ This Message was edited by: macker72 on 2002-08-09 09:23 ]

[ This Message was edited by: Aladin Akyurek on 2002-08-09 09:31 ]

8. Possibly
=IF(ISNUMBER(F4),RANK(F4,F3:F102),"")

This will insure prevention ranking of non-numeric values, which I think is the root problem. Along these lines, you could also change your f4 formula to eliminate text values from flowing through and use the straight rank.

On 2002-08-09 09:15, Macker72 wrote:
this works:
=IF(COUNT(F4),RANK(F4,\$F\$3:\$F\$102),0)

Thanks
Mac

[ This Message was edited by: macker72 on 2002-08-09 09:23 ]

9. On 2002-08-09 09:15, Macker72 wrote:
this works:
=IF(COUNT(F4),RANK(F4,\$F\$3:\$F\$102),0)

Thanks
Mac

[ This Message was edited by: macker72 on 2002-08-09 09:23 ]
LEN and ISNUMBER like COUNT would also work.

ISBLANK would return FALSE, correctly, with "" in F4.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•