# Value error and how to ignore blank cells

[Dannn78]

Hey All,

Im having a bit of an issue. In a excel sheet in the colum i have some
numbers that are copied from another data source. When these are copied into
the sheet Sometimes the cells are blank and hence are preventing the formulas
throughout the workbook to display a #value.(=Summary!F74/Summary!$G74) will
display #value as Summary!F74 is a blank cell

Is there a way I can either make all blank cells have a 0 in them easily or
ignore the error message

i.e.
190

23

3
2
2

18
1
2
2
9
2
3

Thanks

 

[T. Valko]

numbers that are copied from another data source.

That automatically throws up a caution flag!

(=Summary!F74/Summary!$G74) will display
#value as Summary!F74 is a blank cell

If Summary!F74 was an empty cell *and* Summary!$G74 was either a numeric
number or a TEXT number then the result of that formula would be 0. If
Summary!F74 is not truly an empty cell but might contain some kind of unseen
TEXT character then you'll get the #VALUE! error. It's not uncommon to pull
in unseen text characters when you get data from another source, especially
a website.

Test the cell Summary!F74 to see if it is empty:

=CODE(Summary!F74)

If the cell is truly empty that formula will return a #VALUE! error. If the
formula returns some number then the cell is not empty.

I deal with this just about every day. If you do this often you should
download this macro which "cleans" unseen characters from data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

After you paste the data, select the range of cells then run that macro.

 

[JoeU2004]

(=Summary!F74/Summary!$G74) will
display #value as Summary!F74 is a blank cell

Is there a way I can either make all blank cells have a 0 in
them easily or ignore the error message

Try:

=N(Summary!F74) / Summary!$G74

F74 probably is not really empty. It just appears to be blank because it
contains "" (null string) or even some other text.

By the way, if G74 might be zero or blank (empty or ""), it would be prudent
to do the following:

=IF(N(Summary!$G74) = 0, 0, N(Summary!F74) / Summary!$G74)


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