lookup folmula i/e n/a

G

gwbdirect

I am using this formula to get the last value in the column
=LOOKUP(9.99999999999999E+307,L7:L516)
The problem is when it has no value instead of 0 it gives me #n/a.
This is a problem because I have different sheets that have value’s and I
want to add them up for a total and n/a won’t allow me to get a total. Is
there a solution or another formula that would give me a 0 instead of n/a
thanks
 
J

JW

This work?
=IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),
0,LOOKUP(9.99999999999999E+307,L7:L516))
 
J

Joel

=if(isna(LOOKUP(9.99999999999999E+307,L7:L516),0,LOOKUP(9.99999999999999E+307,L7:L516))
 
A

Alan

=IF(ISNA(LOOKUP(9.99999999999999E+307,L7:L516)),"0",LOOKUP(9.99999999999999E+307,L7:L516))

Alan
 
J

Joel

Alan: You should have double quotes around your zero. It is a number not a
string.
 
J

Joel

You would get wrong results if you tried to used the results from your
formula in any arithmetic operation.
 
A

Alan

You can use the "0" string * 1 in another formula.

I appreciate you trying to set me straight, but please pay more attention to
your own posts. Your first attempt at the formula errors out, and your
correction of my formula, "You should have double quotes around your zero",
also errors.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find #N/A in column D 2
Scrabble Value calculation for Welsh words 0
Looping : For... Next problem 1
C.Value #N/A 2
lookup returning #N/A 5
#N/A Conversion 8
Loop Repeats - PLS HELP 2
loop help 6

Top