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

jmaricic

New member
Joined
Apr 18, 2014
Messages
3
Reaction score
0
Points
0
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
 
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)
 
What do you mean by 'collect values'?
 
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 :)
 
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.
 
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.
 
Back
Top