Hi there

trying to do a formula based on true/false output but I cant get round this

Team A(B1) v(D1)Team B

IF SUM(B1D1) say 1 - 0 {if true = 1 if false = 0}
IF SUM(B1<D1) say 0 - 1 {if true = 1 if false = 0}

No problem threre.. BUT

IF SUM (B1=D1) say 0 -0 or 1 -1 {if true = 1 if false =0}OK but
the trouble is if blank in both cells still shows 1 I need it to show 0
false
basically if b1=" " and D1=" " then false = 0

Please help I can't solve it - pulling my hair out what little I have left.

Cheers
Paul

On Wed, 9 Jun 2004 06:55:33 +0100, "paul thomas"
wrote:

Hi there

trying to do a formula based on true/false output but I cant get round this

Team A(B1) v(D1)Team B

IF SUM(B1D1) say 1 - 0 {if true = 1 if false = 0}
IF SUM(B1<D1) say 0 - 1 {if true = 1 if false = 0}

No problem threre.. BUT

IF SUM (B1=D1) say 0 -0 or 1 -1 {if true = 1 if false =0}OK but
the trouble is if blank in both cells still shows 1 I need it to show 0
false
basically if b1=" " and D1=" " then false = 0

Please help I can't solve it - pulling my hair out what little I have left.

Cheers
Paul


Your example is a bit confusing.

You state that if the cells are blank, that you want to show a 0.

But your formula has you looking for a <space (" ") in those cells. If you
truly want the cell to be blank, you need to check with the ISBLANK function.
If you want to check for a null string, you need to use the "" (without a
space) construct.

Obviously if both cells are blank, contain a space, or contain a null string
they will be equal; so you need to explicitly check for what it is you want to
test. One formula that checks for a null string (which test TRUE if the cell
is blank, or if there is a formula in it that returns a null string) is:

=(B1=D1)*(B1<"")*(D1<"")


--ron

Ron

Absolute Hero - Worked a treat.

Thankyou for your help. (Just investing in the old hair restorer now :-)

Paul

"Ron Rosenfeld" wrote in message
...
On Wed, 9 Jun 2004 06:55:33 +0100, "paul thomas"

wrote:

Hi there

trying to do a formula based on true/false output but I cant get round
this

Team A(B1) v(D1)Team B

IF SUM(B1D1) say 1 - 0 {if true = 1 if false = 0}
IF SUM(B1<D1) say 0 - 1 {if true = 1 if false = 0}

No problem threre.. BUT

IF SUM (B1=D1) say 0 -0 or 1 -1 {if true = 1 if false =0}OK but
the trouble is if blank in both cells still shows 1 I need it to show 0
false
basically if b1=" " and D1=" " then false = 0

Please help I can't solve it - pulling my hair out what little I have
left.

Cheers
Paul


Your example is a bit confusing.

You state that if the cells are blank, that you want to show a 0.

But your formula has you looking for a <space (" ") in those cells. If
you
truly want the cell to be blank, you need to check with the ISBLANK
function.
If you want to check for a null string, you need to use the "" (without a
space) construct.

Obviously if both cells are blank, contain a space, or contain a null
string
they will be equal; so you need to explicitly check for what it is you
want to
test. One formula that checks for a null string (which test TRUE if the
cell
is blank, or if there is a formula in it that returns a null string) is:

=(B1=D1)*(B1<"")*(D1<"")


--ron

On Wed, 9 Jun 2004 18:24:44 +0100, "paul thomas"
wrote:

Worked a treat.

Thankyou for your help. (Just investing in the old hair restorer now :-)

Thank you for the feedback. But also consider what you'll save in barber
bills!


--ron