#Value! error on code that should be correct?

[RHein]

I am currently converting lotus 1-2-3 workbooks into excel 2007 workbooks for
my company and I have ran into a road block, It seems that excel will not
automaticly reconize a "blank" or zero and is producing the #Value error The
following will be the code and the cells it links to I would like it to
produce a zero if nothing is in the cell. Please help:
1.)=SUM(C40:F40)<<<<<This is T40
2.)=IF(T40>0,T40,0)<<<<<This is G40, which is 0
3.)=IF(G40>0,IF(N40>0,AI40,M40),0)<<<<Q40
4.)=IF(G40>0,G40,0)<<<<N40, Which is 0
5.)=IF(N40>0,1.3*((N40*P40)^0.625/(N40+P40)^0.25),M40)<<<AI40, Which is a
#Value! error
6.)=IF(L40>0,(L40/(3.5^LOG10(AB40*13.2)*2.04)/1.77)^(1/LOG10(AA40*8)),0)<<<<
That is M40 which is also a Value err.
7.)=IF(G40>0,(T$14/G40)*H$12,0)<<<That is AB40, Which is 0
8.) =IF(L40>0,(1.08*(L40^0.36))/(AB40^0.0009)-(AB40*10.1-2),0) <<< that is
AJ, Which is a Value error

I have most of this calculation sheet completed there is 8 rows of this
problem on 30 sheets I have not been able to crack. I don't understand why
excel doesn't look at a 0 or blank space and calculate as such if your trying
to multiply a "blank" which = 0 (or at least it should be) why it wouldn't
just make that cell a zero or blank. Also sorry about the formating but I
thought with the math it might help to break it down. Its Upper level Trig.

 

[Pete_UK]

Check the cells which you think are blank - they may contain one (or
more) spaces and therefore look blank, but when you try to use them in
arithmetic then Excel will return the #VALUE error. You can check if
A1 is blank (for example) by =LEN(A1) - this should return zero.

Hope this helps.

Pete

 

[ShaneDevenshire]

Hi R,

Without seeing the entire spreadsheet it would be hard for us to know what's
wrong, but Excel does not treat a cell that contains a spacebar as a blank
cell, so if you have any formulas of the type =IF(N40>0,N40," ") formulas
that reference this cell when the value in N40 is not >0 will return an
error. Also, if anyone has cleared cells using Spacebar the cell will appear
empty (blank) but it won't be and it may generate this type of error, for
example, =1/LOG10(B1) will return a VALUE error if B1 contains a spacebar.
Another thing to keep in mind a spacebar is considered to be greater than 0!
Additionally, if you enter =IF(E1=0,"",1) in cell A1 and then test to see if
A1 is >1, it will be if the formula in A1 returns ""

Maybe this will help you find the problem.