#Value Error

[Curtis]

Why am I getting this error when I am trying to find the difference between 2
numbers say a6-a140

Each cell has a formula to pull data however in some cases there is no data

Thanks

 

[Fred Smith]

The #Value errors means that one of your cells is not a number. Excel sees
it as text. Ensure your cells (a6 and a140 in your example) are numbers, and
the #Value error will go away. Using =isnumber(a6) is an easy way to test
for the type of data in the cell.

Often this arises when people put quotes around numbers, typically in an If
statement. This creates text, not numbers, even though there's no
discernable difference displayed.

Wrong way:
=if(a1="","10","6")

Right way:
=if(a1="",10,6)

Regards,
Fred.

 

[Max]

One option to tinker ..
Instead of: =A6-A140
you could try: =SUM(A6)-SUM(A140)
SUM ignores text, hence the above will effectively ignore the "no data"
return in A6 and/or A140
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[JoeU2004]

Why am I getting this error when I am trying to find the difference
between 2 numbers say a6-a140

Each cell has a formula to pull data however in some cases there
is no data

Wrong way:
=if(a1="","10","6")
Right way:
=if(a1="",10,6)

Only after you correct any problems like the one Fred mentions (good
advice), if the problem persists, I suspect that "no data" means a null
string of the form "".

In that case, there are several things you can do, depending on the desired
outcome. The simplest:

=n(A6)-n(A140)

That will treat "" like zero.

Alternatively, perhaps you would like the following:

=if(count(A6,A140)=2, A6-A140, "")

That performs the arithmetic only if both cells have values; otherwise, the
result appears blank.


----- original message -----