Truth Table in Excel

[JimS]

Good Morning,

I'm trying to incorporate a simple truth table. Basically, if x1=1 then y1
and z1=0, if y1=1 then x1 and z1=0, if z1=1 then x1 and y1 =0. Is there a way
to do this?

 

[iliace]

It's been a while since I've studied logic, but don't you need to
define all values of operands for a truth table, and yield the answer
for each using an operator?

In other words, my idea of a truth table is like so, where #(n,m) is
the operator:

X1 Y1 Z1 X1#(Y1,Z1)
0 0 0 0
0 0 1 1
0 1 0 0
0 1 1 1
1 0 0 .... etc.


It's possible to do what you say as far as setting up the table, but I
fail to see how you would actually use it? If you can give an
example, perhaps that would help.

 

[JimS]

In this application I'm trying to have only 1 positive in a given set so my
truth table would look like this:
x1 y1 z1
1 0 0
0 1 0
0 0 1

If any single response is "1" then all others in that row should be "0". No
two columns should be positive in any one row. Does this clear things up or
muddy the waters further?
--
Thanks,
Jim

It's been a while since I've studied logic, but don't you need to
define all values of operands for a truth table, and yield the answer
for each using an operator?

In other words, my idea of a truth table is like so, where #(n,m) is
the operator:

X1 Y1 Z1 X1#(Y1,Z1)
0 0 0 0
0 0 1 1
0 1 0 0
0 1 1 1
1 0 0 .... etc.


It's possible to do what you say as far as setting up the table, but I
fail to see how you would actually use it? If you can give an
example, perhaps that would help.

 

[Tyro]

You have your truth table. Now what?

tyro

In this application I'm trying to have only 1 positive in a given set so
my
truth table would look like this:
x1 y1 z1
1 0 0
0 1 0
0 0 1

If any single response is "1" then all others in that row should be "0".
No
two columns should be positive in any one row. Does this clear things up
or
muddy the waters further?

[/QUOTE]

 

[iliace]

I got that part. The problem is that a cell value can only come from
one source: formula or manual input. It cannot be both. In other
words, if you have the table you show in A1, then A2 would be a
manually entered value. B2 and C2 would have calculated values by
formula.

Along the lines of:

B2 = IF(OR(A2,C2),0)

What's not clear is what the formula should evaluate to if B2 is 0.
How would you determine whether Y or Z should be 1?

Another approach would be to do this programmatically. Let's say you
have an event trigger in your worksheet's code module, as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2:C4")) Is Nothing Then
Dim rng As Excel.Range
Set Target = Target.Cells(1)
If Target.Value = 1 Then
For Each rng In Intersect(Target.EntireRow, Me.Range("A2:C4"))
If rng.Address <> Target.Address Then
rng.Value = 0
End If
Next rng
End If
End If
End Sub

Any time a value of 1 is entered in range A2:C4, the other 2 values in
the same row are set to 0.

Again, there must be greater utility to doing this, but I cannot
gather what it is from your inquiry.

 

[JimS]

I would like to notify the user if there is more than 1 positive in a given
row by changing the cell background color (for example) and gettng the user
to correct the row before going on. Or accepting the last entry and changing
the other row entries.
This is being used in a patient care facility where each patient is
receiving 1 and only one level of care (appropriate for that patient). I am
essentially trying to "bullet-proof" the chart.

Would an IFSUM statement accomplish this?
ex. (IFSUM(x1:z1>1,(what syntax?)

--
Thanks,
Jim

You have your truth table. Now what?

tyro

[/QUOTE]

 

[David Biddulph]

You could use data validation, requiring whole number, less than or equal
to:
=1-OR(X1,Y1) as the formula for Z1, and corresponding formulae for X1 and
Y1.
--
David Biddulph

In this application I'm trying to have only 1 positive in a given set so
my
truth table would look like this:
x1 y1 z1
1 0 0
0 1 0
0 0 1

If any single response is "1" then all others in that row should be "0".
No
two columns should be positive in any one row. Does this clear things up
or
muddy the waters further?

 

[iliace]

A non-intrusive way to do this (meaning, no pop-up dialogs) is using
conditional formatting. From the above example, highlight cell A2,
and enter this rule (Formula is):

=SUM($A2:$C2)>1

Then go to Format and select a color you would like to highlight.
Finally, copy cell A2, select format painter and highlight your entire
data entry range. This will not prevent an error, but it will
highlight the row if there is more than one 1 in it. Of course you'll
have to adjust your range accordingly.

Hope that helps.

 

[Stan Brown]

Mon, 7 Jan 2008 09:23:08 -0800 from JimS

In this application I'm trying to have only 1 positive in a given set so my
truth table would look like this:
x1 y1 z1
1 0 0
0 1 0
0 0 1

If any single response is "1" then all others in that row should be "0". No
two columns should be positive in any one row. Does this clear things up or
muddy the waters further?

You can't do it with formulas, because as soon as you type a value in
any cell, that wipes out the cell formula and then when you change
another cell the first cell won't change. So you'd have to write a
Worksheet_Change macro to sense what changed and adjust the other
cells.

 

[JimS]

Thanks Iliace. I think this is the best option. It seemed like a simple thing
when I first thought about it.