Welcome Guest
Log In | Register )
You last visited January 21, 2017, 4:32 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Question - Blanks

Topic closed. 3 replies. Last post 5 years ago by Teamprofit.

Page 1 of 1
PrintE-mailLink
Avatar
FL
United States
Member #93841
July 8, 2010
576 Posts
Offline
Posted: August 26, 2011, 1:57 pm - IP Logged

How can I create a formula that would give me the 2nd & 3rd non blank cell?

Example.

A1 - blank

A2 - 48

A3 - blank

A4 - 05

A5 - blank

A6 - 20

 

(INDEX(A1:A6,MATCH(TRUE,A1:A6<>"",0)) )

The formula above would give me the first number that is not blank - which is 48

I would like a formula that would give me the second number - which is 05

& the third number - which is 20

 

Is this possible?

"It's evolve or die, really, you have to evolve, you have to move on otherwise it just becomes stagnant."

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    51178 Posts
    Offline
    Posted: August 26, 2011, 2:07 pm - IP Logged

    How can I create a formula that would give me the 2nd & 3rd non blank cell?

    Example.

    A1 - blank

    A2 - 48

    A3 - blank

    A4 - 05

    A5 - blank

    A6 - 20

     

    (INDEX(A1:A6,MATCH(TRUE,A1:A6<>"",0)) )

    The formula above would give me the first number that is not blank - which is 48

    I would like a formula that would give me the second number - which is 05

    & the third number - which is 20

     

    Is this possible?

    Instead of A1:A6<>"" try NOTA1:A6=""

    A mind once stretched by a new idea never returns to its original dimensions!

      Avatar
      FL
      United States
      Member #93841
      July 8, 2010
      576 Posts
      Offline
      Posted: August 26, 2011, 3:15 pm - IP Logged

      Instead of A1:A6<>"" try NOTA1:A6=""

      No, it didn't work

      "It's evolve or die, really, you have to evolve, you have to move on otherwise it just becomes stagnant."

        Avatar
        FL
        United States
        Member #93841
        July 8, 2010
        576 Posts
        Offline
        Posted: August 26, 2011, 4:01 pm - IP Logged

        I used this formula and it works fine..

        =LARGE(A1:A6,1)

        =LARGE(A1:A6,2)

        =LARGE(A1:A6,3)

         

        It gives me the numbers i'm looking for while skipping the blanks :)

        Thanks raven62 for trying to help

        "It's evolve or die, really, you have to evolve, you have to move on otherwise it just becomes stagnant."