Excel 2003 Formula

L

Lee

I have added rows of info using =IF(F139=0,"",F139-I139), however when i
select these formula in the column to add about 12 of them all I get is
#value! Help?!?!?
 
M

Max

Lee said:
I have added rows of info using =IF(F139=0,"",F139-I139), however when i
select these formula in the column to add about 12 of them all I get is
#value! Help?!?!?
Click to expand...

It's because of the zero length null string: "" which is text
being returned by the formulas

2 ways to go ..

With the formulas remaining as-is,
to sum, use SUM, eg: =SUM(J139:J150)
assuming the IF formulas are within J139:J150
SUM will ignore text

Another way is to change the IF to return zeros instead,
ie use: =IF(F139=0,0,F139-I139)

For a neat look, we can always suppress extraneous zeros
from showing in the sheet via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK
 
L

Lee

Max said:
It's because of the zero length null string: "" which is text
being returned by the formulas

2 ways to go ..

With the formulas remaining as-is,
to sum, use SUM, eg: =SUM(J139:J150)
assuming the IF formulas are within J139:J150
SUM will ignore text

Another way is to change the IF to return zeros instead,
ie use: =IF(F139=0,0,F139-I139)

For a neat look, we can always suppress extraneous zeros
from showing in the sheet via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
Click to expand...


Great thanks, worked really well... Thank you for your time
 
Top