Results 1 to 6 of 6

Thread: Formula that returns only value from a cell that is not N/A

  1. #1

    Formula that returns only value from a cell that is not N/A



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

    Hi, I have a table like below and need to collect values that are not n/a in a snigle column. How can I do it with formula or some macro?
    #N/A #N/A #N/A 570333.3 #N/A #N/A #N/A #N/A 570333.3
    #N/A #N/A 507577 #N/A #N/A #N/A #N/A #N/A 507577
    #N/A #N/A #N/A #N/A #N/A #N/A 577621.8 #N/A 577621.8
    #N/A #N/A #N/A 389439.3 #N/A #N/A #N/A #N/A 389439.3
    #N/A #N/A 705569.2 #N/A #N/A #N/A #N/A #N/A
    #N/A #N/A #N/A #N/A 715811.6 #N/A #N/A #N/A

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    if there's only one value not #N/A in the row:
    =INDEX(A1:H1,MATCH(9E+99,A1:H1,1))
    (it actually returns the rightmost non-#N/A value)

  3. #3
    What do you mean by 'collect values'?

  4. #4
    Quote Originally Posted by p45cal View Post
    if there's only one value not #N/A in the row:
    =INDEX(A1:H1,MATCH(9E+99,A1:H1,1))
    (it actually returns the rightmost non-#N/A value)
    It works no matter I don't understand this 9E+99... Thanks :-)

  5. #5
    probably my expression wasn't correct...what I needed was take the value that is not N/A and place it in a separete column so I could get all values in one column.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by jmaricic View Post
    I don't understand this 9E+99... Thanks :-)
    It's a big number, probably bigger than any other value on the row, which it tries to get a match for. Check out the Help for the Match function.

Posting Permissions

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