Generate a truth table in excel

Question

I need to make a formula that gives you the truth table for a variable number of columns.

Example

Solution 1

Replace the FirstCell with a static reference to the cell that contains the first 2^1 value e.g. $D$1 for a 4-bit table (16 values) and autofill to the rest of the grid (in the example A1:D16)

=IF(MOD(ROW()-ROW(FirstCell),POWER(2, ((COLUMN() - COLUMN(FirstCell)) * -1) + 1)) >= (POWER(2, ((COLUMN() - COLUMN(FirstCell)) * -1) + 1) / 2),1,0)

The logic behind this is:

If the current row modulus 2 power current column (* -1 as the first value is in the last column and + 1 because it starts from 0) is greater or equal to half of 2 power current column, put the value as 1, else put the value as 0.

Solution 2

The current recommended answer did not work for me. For a simpler method, I'd recommend the following formula:

=IF(MOD(FLOOR((ROW()-ROW(TopRight))/(2^(COLUMN(TopRight)-COLUMN())), 1),2)=0,0,1)

Where TopRight is the top right cell of the truth table.

For instance, if you're creating a truth table with 8 entries that starts in A3, replace TopRight with $H$3, then drag the formula across and down.

---

A basic explanation of what's going on: In truth tables, the rows alternate 1 or 0 every 2 ^ n number of rows, where n is the the number of columns that the given column is away from the rightmost column.

Solution 3

The other answers might make Boole sad. This one aims to be more boolean.

You need to populate the first row (2) with 0's

For the LSB column (D) - Invert:

• =NOT(D2)*1 (formula for cell D3, copied to D4:D17)
• That will invert the value from the row above. The *1 numification is necessary to avoid seeing TRUE or FALSE

For all other columns - Add:

• =XOR(AND(D2:$D2),C2)*1 (formula for cell C3, copied to all cells A3:C17)
• For an ADD function, you want to XOR the value above in the column with the result of ANDing all the bits in all the columns to the right of it. (In other words: if all the bits to the right of the bit above are 1, then you should flip the value from the bit above. This ADD formula works for any number of columns.)
• The AND range is referenced to one row up and one col right, to the $D LSB column, also one row up. So the $D anchor for the LSB column allows copying to any other column
• Again, *1 is used for numification of the resulting TRUE/FALSE

Comments

[janv8000]

Heads-up for those using a regional setting with , as decimal separator => replace occurrences of , with ;

[tcop]

Really helpful! For everyone else, note that "FirstCell" is the top Right Cell of the table.