how to do a rank and iserror together

[saurabh_sharma]

hi
I wish to use the iserror function so that a blank cell shows up instead of showing a #value! error. This is to be used along with a rank function

 

[Chris Davison]

=ISERROR will trap *any* error, which may be too blase....

=ERROR.TYPE will allow you to specify which error to look for specifically, with #VALUE returning a result of 3

so =IF(ERROR.TYPE(your formula)=3,your blank,your formula)

 

[saurabh_sharma]

thanks for the help

On 2002-04-26 19:44, Chris Davison wrote:
=ISERROR will trap *any* error, which may be too blase....

=ERROR.TYPE will allow you to specify which error to look for specifically, with #VALUE returning a result of 3

so =IF(ERROR.TYPE(your formula)=3,your blank,your formula)

 

[Aladin Akyurek]

Given that A1:A7 houses the sample below:

{3;
2;
20;
2;
"";
9;
10}

where "" stands for a blank (empty cell),

the following RANK formula will ignore the blank:

in B1 enter and copy down:

=IF(A1,RANK(A1,$A$1:$A$7),"")

If your blanks are formula generated blanks and/or true blanks, use:

=IF(ISNUMBER(A1),RANK(A1,$A$1:$A$7),"")

Aladin