The opposite of IF function gives #VALUE for value = 0
 

Hi guys,

In the 1st cell (Input!C11) I have 0.00

In the 2nd cell (on another worksheet, merged from 4 cells: F10:I10), I have
a following formula:
=IF(Input!C11<0,Input!C11,"") -- visually, I have a blank cell (that's OK)

Then, in the 3rd cell (once again merged from 4 cells B11:E11, same
worksheet as the 2nd one), I have
=-F10 -- visually I would expect to have also a blank cell, but for some
reason I receive #VALUE. This is only if the Input!C11 = 0.00, for other
values it makes the calculations correctly.

(Moreover, if I modify the formula and put =F10 (positive), then there is no
problem, the cell is blank)

Could you help me on this?
Thanks a lot!

Hi

Enter any positive number into Input!C11, and you get same error - because
you are trying to calculate a negative of empty string :-))

Try
=IF(ISNUMBER(F10);-F10;"")


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"gizmo" wrote in message
...
Hi guys,

In the 1st cell (Input!C11) I have 0.00

In the 2nd cell (on another worksheet, merged from 4 cells: F10:I10), I
have
a following formula:
=IF(Input!C11<0,Input!C11,"") -- visually, I have a blank cell (that's
OK)

Then, in the 3rd cell (once again merged from 4 cells B11:E11, same
worksheet as the 2nd one), I have
=-F10 -- visually I would expect to have also a blank cell, but for some
reason I receive #VALUE. This is only if the Input!C11 = 0.00, for other
values it makes the calculations correctly.

(Moreover, if I modify the formula and put =F10 (positive), then there is
no
problem, the cell is blank)

Could you help me on this?
Thanks a lot!

Thanks Arvi, your solution works 100% OK!

"Arvi Laanemets" wrote in message
...
Hi

Enter any positive number into Input!C11, and you get same error - because
you are trying to calculate a negative of empty string :-))

Try
=IF(ISNUMBER(F10);-F10;"")


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"gizmo" wrote in message
...
Hi guys,

In the 1st cell (Input!C11) I have 0.00

In the 2nd cell (on another worksheet, merged from 4 cells: F10:I10), I
have
a following formula:
=IF(Input!C11<0,Input!C11,"") -- visually, I have a blank cell (that's
OK)

Then, in the 3rd cell (once again merged from 4 cells B11:E11, same
worksheet as the 2nd one), I have
=-F10 -- visually I would expect to have also a blank cell, but for
some
reason I receive #VALUE. This is only if the Input!C11 = 0.00, for other
values it makes the calculations correctly.

(Moreover, if I modify the formula and put =F10 (positive), then there
is
no
problem, the cell is blank)

Could you help me on this?
Thanks a lot!