using the IF statement

[DontKnow]

Hi Guys,

if I want to set up a a sequencve ogf numbers in rows like this:

A B c Resultant
1 1 0 1
0 1 1 0
0 1 0 0
1 0 1 1

Where the resulatant output only provides a high (1) for numbers in columns
(A and C) or (A and B) and every other combination a low (0), how would I
program something like this?? As shown above!!



Please help me as I am stuck

Cheers

Ian

 

[Bob Bridges]

Hmm... You mean the below is a truth table and you want the If statement for
A, B and C that produces this result? But we're missing part of the table.
Let me redraw it like this and you fill in the missing parts:

TRUE,TRUE,TRUE:?
TRUE,TRUE,FALSE:TRUE
TRUE,FALSE,TRUE:TRUE
TRUE,FALSE,FALSE:?
FALSE,TRUE,TRUE:FALSE
FALSE,TRUE,FALSE:FALSE
FALSE,FALSE,TRUE:?
FALSE,FALSE,FALSE:?

The If statement that produces the results you want depends on how you fill
in the missing parts.

 

[DontKnow]

I hope this helps

Hmm... You mean the below is a truth table and you want the If statement for
A, B and C that produces this result? But we're missing part of the table.
Let me redraw it like this and you fill in the missing parts:

TRUE,TRUE,TRUE:? False
TRUE,TRUE,FALSE:TRUE
TRUE,FALSE,TRUE:TRUE
TRUE,FALSE,FALSE:? False
FALSE,TRUE,TRUE:FALSE
FALSE,TRUE,FALSE:FALSE
FALSE,FALSE,TRUE:? False
FALSE,FALSE,FALSE:? False

The If statement that produces the results you want depends on how you fill
in the missing parts.

 

[Bob Bridges]

Yes, it does. Ok, you said it the way you meant it (not everyone always
does): you want True only if A is True (or 1) and B is True (or 1) and in
every other case you want the result to be False. You don't need a program
for this - you can do it with Excel worksheet functions - but since you
specified a VBA program the statement would look something like this, I guess:

With Myworksheet
For ir = 2 to BottomRow
.Range("D1") = .Range("A1") And (.Range("B1") Xor .Range("C1"))
Next ir
End With

That's if you're using True and False rather than 1 and 0. If you have to
go with 1 and 0 it looks slightly more complicated but is really the same:

With Myworksheet
For ir = 2 to BottomRow
.Range("D1") = .Range("A1")=1 And (.Range("B1")=1 Xor .Range("C1")=1)
Next ir
End With

"A Xor B" means "if A is true or B is true but not both".

 

[DontKnow]

Sorry Bob,

I can't find an XOR function anywhere in Excel??

I have also tried EXOR still no luck!!

Cheers,

THanks for your help in advance!!

 

[Rick Rothstein \(MVP - VB\)]

If he is using 1 and 0, you can use this to simplify the expression a
little...

..Range("D1") = -.Range("A1") And (-.Range("B1") Xor -.Range("C1"))

Note the minus signs in front of each Range expression for those ranges to
the right of the equal sign.

Rick

 

[DontKnow]

Yes Rick,
I was trying to use the worksheet functions, rather than programming to
determine how to use the EXOR function!!

Mnay thanks for your input!!

 

[Rick Rothstein \(MVP - VB\)]

The code Bob gave you is VBA code, not worksheet formulas... the reason he
gave you VBA code is because you posted in the excel.programming newsgroup
plus you used the words "how would I program something like this" in your
original message. I'm guessing from your response that you do not want VBA
code. There is no XOR function in Excel, but this worksheet formula should
do what you want...

If you are using 1s and 0s
********************************
=--AND(A1=1,OR(B1=1,C1=1),B1<>C1)

If you are using TRUEs and FALSEs
********************************
=AND(A1,OR(B1,C1),B1<>C1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

See my other response...

Rick

Yes Rick,
I was trying to use the worksheet functions, rather than programming to
determine how to use the EXOR function!!

Mnay thanks for your input!!

 

[DontKnow]

All good mate thanks for your time,

Cheers

Thnaks for your help Bob and Rick!!