Rank function

[Mycotopian]

how can I replace the #N/A with 0 or even a blank cell in my results
from the following statement =RANK(H4,$H$15:$H$30,)

 

[Frank Kabel]

Hi
try the following
=IF(ISNA(RANK(H4,$H$15:$H$30,),"",RANK(H4,$H$15:$H$30,))

Frank

 

[Harlan Grove]

how can I replace the #N/A with 0 or even a blank cell in my results
from the following statement =RANK(H4,$H$15:$H$30,)

Is this due to there being #N/A values in $H$15:$H$30 or due to H4 not appearing
in $H$15:$H$30? If the former and you wanted the rank of H4 in the non-#N/A
cells in $H$15:$H$30, you could use

=IF(COUNTIF($H$15:$H$30,H4),1+COUNTIF($H$15:$H$30,">"&H4),"")

If the latter, then

=IF(COUNTIF($H$15:$H$30,H4),RANK(H4,$H$15:$H$30,H4),"")

Actually, the first IF formula above would handle both circumstances, so you may
as well just use it.

 

[Mycotopian]

Perfect! thanks guys

 

[Mycotopian]

Hey guys I am having a siumilar problem but the formulas you gave me are
not working in this situation. I need to get rid of that #VALUE!


units------Rank

#VALUE!
#VALUE!
43 4
29 5
54 2
46 3
#VALUE!
120 1
#VALUE!
#VALUE!

 

[Harlan Grove]

...
...

units------Rank

#VALUE!
#VALUE!
43 4
29 5
54 2
46 3
#VALUE!
120 1
#VALUE!
#VALUE!

...

So the units column contains blank cells in the rows corresponding to #VALUE!
errors in the Rank column? If the units column, heading and blank cells along
with numbers, were in A1:A12, then enter the following formula in B3.

=IF(COUNTIF($A$3:$A$12,A3),1+COUNTIF($A$3:$A$12,">"&A3),"")

This evaluates to "" on my machine. Then fill B3 down into B4:B12. B3:B12
evaluates to {"";"";4;5;2;3;"";1;"";""} on my machine. Aside from changing the
ranges, this is the first formula I gave in my previous response, so it seems a
fair bet you didn't try it. Now would be a good time to do so.

 

[Mycotopian]

I attached a jpeg of the formula in actio

Attachment filename: excel.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=40519

 

[Harlan Grove]

I attached a jpeg of the formula in action

Attachment filename: excel.jpg

Since I don't open attached files, EVEN JPEGs, you want to try to state the
problem in plain text?

 

[Mycotopian]

this is what I get with that formula. Instead of the cells being blank
they are assigned a value of 1.


Units_________Rank
Blank_________1
Blank_________1
43___________4
29___________5
54___________2
46___________3
Blank_________1
120___________1
Blank__________1
Blank_________1

 

[Jonathan Rynd]

Since I don't open attached files, EVEN JPEGs, you want to try to
state the problem in plain text?

Do you browse the web?

It wasn't really an attached file; it was just a link to a website.
Anytime you go to a web page you are running the same risk.

 

[Mycotopian]

By the way the above attachment is a jpeg i pulled using Snagit so I
assure you its safe.

 

[Mycotopian]

I sent it to (e-mail address removed)

 

[Harlan Grove]

Do you browse the web?

It wasn't really an attached file; it was just a link to a website.
Anytime you go to a web page you are running the same risk.

Granted with respect to JPEGs. However, JPEGs don't show what's actually in the
cells, and in general files have lower information content than consise
explanations.

 

[Harlan Grove]

I sent it to (e-mail address removed)

And I especially don't open any attachments coming via e-mail. I read text and
*ONLY* text.

 

[Harlan Grove]

...
...

this is what I get with that formula. Instead of the cells being blank
they are assigned a value of 1.

Units_________Rank
Blank_________1

...

OK, this was all the information I needed.

You say 'blank', but you really mean cells evaluating to strings of zero or more
space characters. Change the A3 formula to

=IF(ISNUMBER(A3),1+COUNTIF($A$3:$A$12,">"&A3),"")

then fill A3 down as far as needed.

 

[Mycotopian]

Thanks Harlan. I still got errors when there were ties but Its good
enough for now.

 

[Harlan Grove]

Thanks Harlan. I still got errors when there were ties but Its good
enough for now.

What errors? #VALUE! ? Shouldn't happen.