if and / or

[april]

col a col b col c
6000
-- 6000
i have a this formula in Col C if(a>0,"debit","credit"). however, there are
rows where there is nothing in columns A or B so i want Col C to be blank

thanks in advance for your help

aprilshowers

 

[Fred Smith]

Assuming the formula you posted just has a typo, use:

=if(count(a1:b1)<2,"",if(a1>0,"debit","credit"))

Regards,
Fred

 

[JLatham]

Try this formula for row 2
=IF(A2>0,"debit",IF(B2>0,"credit",""))
and fill down.

 

[april]

well your solution is certainly a lot simpler than the one i finally came up
with =IF(AND(ISBLANK(D9),ISBLANK(E9))," ",IF(D9>0,"debit","credit"))

thanks for your help

 

[JLatham]

There are usually several ways to solve any given problem in Excel. Unless
you're really pressing for the ultimate in speed and 'economy', then my rule
of thumb is:
if it works, and you understand it and can maintain it, use it.

 

[JLatham]

One note, and it was sparked by a comment 'Mike H' made:

you seem to have a (bad) habit if inserting a
space
" " instead of a null string "" for the false condition. As a general rule
it is better to return a NULL string for your false condition.

I agree with him on that, as does David Biddulph, see this discussion:

http://www.microsoft.com/office/com...misc&mid=e3db734d-f191-4b94-aa3b-53a7e400e3b9

As David Biddulph explained in that same discussion:
There is a difference between a space " " and the null string "". Strictly
neither of these is the same as a blank cell, but a test for ="" will return
TRUE for either a blank cell or a cell in which "" is returned by a formula,
so I would support Mike's recommendation that you use "", and not " ".