On 27/6/06 15:17, in article , "Franz
Verga" wrote:
Nel post
*Jay* ha scritto:
On 27/6/06 10:17, in article ,
"Franz Verga" wrote:
Nel post
*Jay* ha scritto:
Or - the second option you mentioned (IF..)
=IF(ISNA(VLOOKUP(A1,range,col,index)),"0",VLOOKUP( A1,range,col,index))
Which will return a zero instead of an #N/A.
The statement checks for an N/A error and returns a 0.
Help search on ISNA for more details.
HTH
-Jay-
No Jay, your formula will not return a zero instead of an #N/A, but
a string which has just one character 0, so should be better write 0
without quotes: quotes are needed just for text, not for numbers.
Thanks Franz, I didn't realise I'd done that - (wrote it without
thinking).
Am I correct in thinking the 0 string could still have been
recognised as a number though, for the SUM?
-Jay-
No, it's note recognized, just skipped like blanks or null strings, also
words: you can try this;
input: A1 == 56, A2 == ="", A3 == ="0", A4 == mouse, A5 == 21 A6 ==
=SUM(A1:A5)
The result in A6 is 77...
Well it would be *even* if the 0 string was recognised as a number, with it
being zero, but I know what you mean - 'Number' Text strings aren't
recognised as numbersand skipped like nulls. However, can you explain this:
A4 == 7
A5 == "2"
SUM(A4:A5) gives 7 (As expected,the "2" string isn't recognised as a num)
=A4*A5 gives 14
=SUM(A4*A5) gives 14
Why is the "2" text string not recognised as a number by the function, but
*is* when used in a direct mathematical operation, A4*A5 ?
-Jay-