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

debit	credit
	10
	20
	30
	40
	50
10
20
30
40
50

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Details Sheet

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

result
-10
-20
-30
-40
-50
10
20
30
40
50

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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.