empty cells issue - possible formatting or formula issue

[lezawang]

Hi

I have a file which has 2 sheets. One is called FORM and another called Details. in the Details sheet, in the second cell, I have the following formula:

=IF(FORM!A2<>0,FORM!A2,IF(FORM!B2<>0,-FORM!B2,0))

Everything is working fine. I created these tables by myself in a new file but I have similar situation with a real file (not mine) which has exactly the same 2 sheets. What is the problem with the real file, if I do not put 0 in the "debit" then the Result column (first 5 cells) will be empty. I checked the format of all cells, Debit and Credit columns are type of Custom, while the Result column is type of Number. I tried to change all to different formats but could not get it work

FORM sheet
[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]debit[/TD]
[TD="width: 64"]credit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Details Sheet

The formula in the second cell is =IF(FORM!A2<>0,FORM!A2,IF(FORM!B2<>0,-FORM!B2,0))

[TABLE="class: grid, width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]result[/TD]
[/TR]
[TR]
[TD="align: right"]-10[/TD]
[/TR]
[TR]
[TD="align: right"]-20[/TD]
[/TR]
[TR]
[TD="align: right"]-30[/TD]
[/TR]
[TR]
[TD="align: right"]-40[/TD]
[/TR]
[TR]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

but I am just curious now, why you decided to put -- infront of the formula?

Because when I dragged the formula down the positive results were numbers stored as text (which I don't like unless I have to).

The double negation just converts it to real numbers (or converts TRUE/FALSE to 1/0) in the same way that adding a +0 or *1 to the formula or wrapping the whole formula in a VALUE() function would.

Double negation is supposedly just fractionally more efficient than the other methods.