+ Reply to Thread
Results 1 to 3 of 3

to change a descending rank formula that ignores '0's to one that ignores blank cells

  1. #1
    Registered User
    Join Date
    07-02-2012
    Location
    South Korea
    MS-Off Ver
    Excel 2010
    Posts
    30

    to change a descending rank formula that ignores '0's to one that ignores blank cells

    My current descending rank formula ignores '0''s

    =OFFSET(S$6,MATCH(SMALL(V$6:V$25,ROW()-ROW(V$6)+1),V$6:V$25,0)-1,0)

    but I need it to ignore blank cells instead?

    Can anyone help, please?

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: to change a descending rank formula that ignores '0's to one that ignores blank cells

    Not sure how you work out that the formula ignores 0's, they will still be returned as the lowest ranking value(s).

    =IF(ROWS(V$6:V6)>COUNT($S$6:$S$25),"",LARGE($S$6:$S$25,ROWS(V$6:V6)))

    Will ignore blanks and prevent the #NUM! errors that your formula returns.

  3. #3
    Registered User
    Join Date
    07-02-2012
    Location
    South Korea
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: to change a descending rank formula that ignores '0's to one that ignores blank cells

    I am attaching the file so you can see what I am trying to do.

    The worksheet in question is 'Totals' and the columns that are causing problems are N, V-Z
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1