Results 1 to 4 of 4

Thread: If values in a column <> 0 then rank them in ascending order

  1. #1

    Post If values in a column <> 0 then rank them in ascending order



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    I need a function that checks whether values in a range are different from 0 and if so, numbers them. This needs to happen for every n+1 value in the range. So the rank in column B changes depending on the values of column A.

    Example:
    A B
    0 0
    1 1
    7 2
    0 0
    3 3


    Any ideas?

    Thanks

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello
    Try this in B2 and copy down:
    =IFERROR(IF(A2=0,0, LARGE($B$1:$B1,1)+1),1)

    This assumes that your data starts on Row 2. If it starts on row 1, do exactly the same, but then copy the formula up to B1 as well.

  3. #3
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Alternatively =IF(A1=0,0,COUNTIF($A$1:A1,">0")) dragged down

  4. #4
    Great!

    Thanks to both!

Posting Permissions

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