multiplying by blank cells input by "IF" functions

[Guest]

I've created an "if" function such that a blank is placed in the cell for a
false answer, however, these result in errors when multiplication formulas in
other cells reference these blanks. The value of the " " I have the "IF"
function input is not "0" and therefore subject to an error. How do I get
multiplication formulas to operate on " " as "0", or, what should I have the
"if" function put in the cell that would appear blank but can be
arithmetically operated on. excel 2003.

 

[Guest]

One option would be to use Conditional Formatting on that cell to change the
text color to white (or whatever your background color is) if the cell is
equal to zero. That way, the value 0 would be in the cell for use in other
calculations, but would appear to be blank on screen/printout.

HTH,
Elkar

 

[Roger Govier]

Hi

It sounds as though you are using space " " rather than Null "" in your
formulae which could give you problems in other ways.
Just a thought for the future.
Do not use =IF(B5="A",1," ") use =IF(B5="A",1,"")

With regard to your immediate problem in trying to multiply cells, you
will get #VALUE if you try to multiply Null by a number, so instead use
=IF(B5="A",1,0)
Do NOT put quotes around the 0 as in "0" or you will have the same
problem.

To hide the zero's choose Tools>Options>View> and untick Zero values