True / false output problem

[paul thomas]

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(B1>D1) 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

 

[Ron Rosenfeld]

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(B1>D1) 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

 

[paul thomas]

Ron

Absolute Hero - Worked a treat.

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

Paul

 

[Ron Rosenfeld]

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