easy question about #VALUE!

[Harvey Waxman]

In D12 I have this: IF(C12<>"", A1*A2,"").

As long as the C12 has a value D12 is ok but if C12 is blank I get #VALUE! in
D12. I know there must be an easy way to fix this. All the values are numbers
except for C12 which can be anything.

Thanks for any help

 

[JE McGimpsey]

In D12 I have this: IF(C12<>"", A1*A2,"").

As long as the C12 has a value D12 is ok but if C12 is blank I get #VALUE! in
D12. I know there must be an easy way to fix this. All the values are
numbers
except for C12 which can be anything.

Thanks for any help

Remove the text from A1 and A2. If A1 or A2 have text (including if they
were "cleared" by typing a space character), the * operator will return
#VALUE!

If you have "numbers" in A1 and A2, they may have been entered as Text.
Format them as General or another number format and reenter them.

 

[Bob Greenblatt]

Remove the text from A1 and A2. If A1 or A2 have text (including if they
were "cleared" by typing a space character), the * operator will return
#VALUE!

If you have "numbers" in A1 and A2, they may have been entered as Text.
Format them as General or another number format and reenter them.

What I do in cases like this is:
IF(len(C12)>0, A1*A2,"")

 

[JE McGimpsey]

What I do in cases like this is:
IF(len(C12)>0, A1*A2,"")

That's my preferred method as well, but it still gives #VALUE! if A1 or
A2 have text. To wear belt and suspenders, this will work:

=IF(LEN(C12)>0,IF(COUNT(A1:A2),A1*A2,""))

or

=IF(LEN(C12)>0,PRODUCT(A1,A2),"")

The latter returns 0 if one of the arguments is text. Note that this can
be quite confusing if another number was entered as text.

 

[Harvey Waxman]

What I do in cases like this is:
IF(len(C12)>0, A1*A2,"")

Same problem. #value! shows up if the cell is empty.

thanks though.

OSX3.2

 

[Harvey Waxman]

If you have "numbers" in A1 and A2, they may have been entered as Text.
Format them as General or another number format and reenter them.

The cell is formatted as a number and when blank, returns #value!

 

[JE McGimpsey]

The cell is formatted as a number and when blank, returns #value!

Enter

=LEN(A1)+LEN(A2)

in a blank cell - is the result 0?

If not, you've got a space character or other non-printing text in A1 or
A2.

If so, I have no idea why you'd get #VALUE! returned. Do you get the
proper result if A1 and A2 have numeric values?

 

[Harvey Waxman]

Enter

=LEN(A1)+LEN(A2)

in a blank cell - is the result 0?

The result is the correct length. Whether the cell is blank or not. The
correct length.

If not, you've got a space character or other non-printing text in A1 or
A2.

If so, I have no idea why you'd get #VALUE! returned. Do you get the
proper result if A1 and A2 have numeric values?

In posting the problem I might have simplified it too much.


Cell D32 has a number
Cell D33 has a formula =IF(C33<>"",0.2*D32,"")

Cell D34 = D32-D33

If C33 has anything in it D34 shows the correct number
If C33 is blank D34 shows #value!

If I put len(d32)+len(d33) into D34 the value is always correct no matter what
is in C33

Hope That is clearer.

 

[JE McGimpsey]

In posting the problem I might have simplified it too much.


Cell D32 has a number
Cell D33 has a formula =IF(C33<>"",0.2*D32,"")

Cell D34 = D32-D33

If C33 has anything in it D34 shows the correct number
If C33 is blank D34 shows #value!

Aaah...

If C33 is blank, then the result of the formula in D33 is text (the null
string, ""). When you try to use Text in an arithmetic operation (e.g.,
subtraction), the result is #VALUE!

One workaround:

in D34: =IF(D33="", D32, D32-D33)


Another:

Enter this in D33:

=IF(C33<>"",0.2*D32,0)

and use Conditional Formatting to change font color to be the same as
the background if the cell value is 0.

 

[Harvey Waxman]

Aaah...

If C33 is blank, then the result of the formula in D33 is text (the null
string, ""). When you try to use Text in an arithmetic operation (e.g.,
subtraction), the result is #VALUE!

One workaround:

in D34: =IF(D33="", D32, D32-D33)


Another:

Enter this in D33:

=IF(C33<>"",0.2*D32,0)

and use Conditional Formatting to change font color to be the same as
the background if the cell value is 0.

Two good solutions, thanks