Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: how to do a rank and iserror together

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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)
    :: Pharma Z - Family drugstore ::

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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