Keeping getting a #Value error

[NateBuckley]

Hello, another day another random problem.

I have just fixed an issue where, I have from someones spreadsheet, two
values.
A
1: 4
2: 4
3: FORMULA

in forumula =SUM(A3-A4)

If A2 is blank then A3 will come up with #VALUE error, unless I go through
and press DELETE key on the blank Cell in A2.

I didn't create this sheet, but is this #Value appearing before I press
delete, because they've used the SUM function to subtract?

Not a huge issue, just curious because I keep having to fix things coming
from a different department and this #Value issue is bugging me because I
don't know why it does this.

Cheers!

 

[DarkQuantity]

Hi Nate,

The problem in fact is that cell A2 is NOT empty. You can test this by
putting the formula =LEN(A2) in B2. The result will be most likely 1.
I think you have a colleague in the other department who thinks that
the way to clear a cell is by typing a space into it. And thought this
might look empty to the eye, to Excel you have text in the cell. So
either 'train' your colleague, or change the formula in A3 to =A1-
IF(ISERROR(VALUE(A2)),0,VALUE(A2)).

dq

 

[Per Jessen]

Hello, another day another random problem.

I have just fixed an issue where, I have from someones spreadsheet, two
values.
     A
1:  4
2:  4
3: FORMULA

in forumula =SUM(A3-A4)

If A2 is blank then A3 will come up with #VALUE error, unless I go through
and press DELETE key on the blank Cell in A2.

I didn't create this sheet, but is this #Value appearing before I press
delete, because they've used the SUM function to subtract?

Not a huge issue, just curious because I keep having to fix things coming
from a different department and this #Value issue is bugging me because I
don't know why it does this.

Cheers!

Hi

You get this error because A2 is containing a "space" charcter. Try to
put any letter in in A2 and you will have the same error.

The only way to avoid this error is to press delete to clear the
content of the cell as you did or to enter at zero value.

Best regards,
Per

 

[NateBuckley]

Ahhhh that makes perfect sense.

Thank you very much!