Results 1 to 9 of 9
  1. #1
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    I can't seem to get the Rank function to work with group of non-contiguous cells. Is there a way to do this?

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can do this by assigning the ranges to a Named Range. Then using =RANK(#,RangeName)

  3. #3
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    I've never used Name Ranges. That works nicely, thanks!

  4. #4
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    I just found a problem that comes up with this. On this spreadsheet, there are sections for up to 20 different items that I am comparing. So the Named Range has 20 cells. But the unused ones need to be deleted from the sheet, so once that deletion is done, the named range refers to some cells that are no longer on the sheet, and the rank result is then #REF. I can't just hide the unused sections, that still creates problems with the ranking since there is no value entered in some cells in the Named Range.

    Is there any way around this, or something I can use instead of Name Ranges?

    Thanks

  5. #5
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    Or is there some kind of "placeholder" that I can put in a cell such that if no value is entered, Excel will ignore it rather than give #REF? That way I could just hide the unused sections rather than delete them.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The #REF is an indication that the cell is or range used in a formula is not valid.

    I'm not sure what you mean by a placeholder. If there is not value in a cell, RANK ignores the cell.

    Can you post a sample of what your sheet?

  7. #7
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    OK I wasn't quite right in what I said. Let's start over.

    The problem is that the unused cells in the Named Range have #VALUE in them (because some other cells related to it are blank), and that is what creates the problem with the RANK function using that Named Range.

    I can't really post the file because it is a company owned spreadsheet. If need be I can create a similar one to post.

    Thanks

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For the cells with th #Value, use a formula like this:

    =If(iserror(yourcurrent formula),"",your current formula)

  9. #9
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    OK that function worked very well for this!

    Thanks for the help.

Posting Permissions

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