Get "####" when use alpha char in a cell referenced by a formula

[Leo]

On a timesheet, if a person did not work they have to type in why. However
this results in a "####" in the 'Total Hours' cell that contains the formula.
If using alpha characters in a cell that is referenced by a formula, how can
you get the result to just be a blank cell, and not the "####"? Thank you!

 

[Rick Rothstein]

What is the formula that is doing this?

 

[Dave Peterson]

If you widen that column, I bet you'll see an error (#Value!, maybe).

If that's correct, then I'd guess that the resolution would depend on what the
formula is that returns that error.

Maybe something as simple as:

=sum(a1:a2)
or
=n(a1)+n(a2)

 

[Mike H]

Leo,

I suspect what it is actually producing is a #VALUE! error and you can't see
it because the cell is too narrow. To avoid this use an error trap around
your formula

=IF(ISERROR(your formula),"",your formula)

Mike

 

[Glenn]

On a timesheet, if a person did not work they have to type in why. However
this results in a "####" in the 'Total Hours' cell that contains the formula.
If using alpha characters in a cell that is referenced by a formula, how can
you get the result to just be a blank cell, and not the "####"? Thank you!

=IF(ISNUMBER(YourCell),YourFormula,"")

 

[Chip Pearson]

Typically, you get the ### characters when you attempt to display a
time with a negative value. Without seeing the formula you are using,
it is difficult to give an answer. However, you can use the ISNUMBER
function to test whether a cell has a numeric value. E.g.,

=IF(ISNUMBER(A1),A1,"Not Number")

Here, if A1 is numeric, it will be displayed. If A1 is not numeric,
the text "Not Number" will be displayed. Beyond that, it is hard to
propose a solution without seeing the formula that is causing the
problem.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)