How to ignore blanks in clacs ?

[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

 

[Guest]

Not sure if my previous reply went through as I got booted!

So try...=IF(B1="","",IF(B1>D1,1,IF(B1<D1,1,0)))

Harry

 

[Stephen Dunn]

Hi Paul,

I'm easily confused, so I'm having difficulty following your logic here...
Are you saying that you want a result of 1 if B1>D1, and a result of 0 if
B1<D1, if both are blank, or if both are equal?

All you then need is:

=--(B1>D1)

Another way I read your question is that you want a result of 1 if B1<>D1,
and 0 for all other cases:

=--(B1<>D1)

If you find that Harry is closer to the answer, then you require:

=IF(B1="","",--(B1<>D1))

which is simply a shortening of Harry's answer. The problem with that is
that it ignores D1="", so:

=IF(AND(B1="",D1=""),"",--(B1<>D1))

However, you may in fact mean:

=IF(OR(B1="",D1=""),"",--(B1<>D1))

but now I'm starting to get lost in my own logic... Please try these to see
which is closest to the answer you require, then consider re-wording your
query.

(be careful entering --, it is two negative signs not an m-dash.)

HTH
Steve D.

 

[paul thomas]

Hi Harry

Thanks for your asistance

The following formula did want i required
=(B1>=D1)*(B1<>"")*(D1<>"")

Thankyou for your help!

 

[paul thomas]

Hi Stephen

Thanks for your asistance

The following formula did want i required
=(B1>=D1)*(B1<>"")*(D1<>"")

Thankyou for your help!

Paul