This is the second part of a tutorial targeted at modeling logic gates in MS Excel 2003.

The first part introduced the basic logic gates,, symbols and associated logic equations. This model includes no delay or loading for the gates.

The following type of gates will are described: inverter, AND, NAND, OR, NOR, XOR (exclusive or) and XNOR.

The tutorial has several parts and the last part builds up to a more complex and accurate model including effects such as: power supply voltage, output impedance and input capacitance (from which low pass fillter effects will be derived within the model). This is not an introduction to digital electronics.

The reader should get somewhat familiar with the topic before attempting to build the models described here.

## Introduction to Digital Electronics – Ideal Logic Gates – Part#2

by George Lungu

– This tutorial is the second part of the series introducing the most common logic gates and

their implementation in Excel, first as ideal static models and later using more realistic

animated models which include propagation delay, loading, strength, power supply voltage.

– In the first part we talked about the most common logic gates (INV, AND, NAND, OR, NOR,

XOR, XNOR), their logic equations, their truth tables and we went forward to implementing a

first iteration of a model in Excel 2003.

– This section will finalize the previously started worksheet using purely worksheet formulas for

the gate models. In the last part of this section user defined (custom) function will be created

for each of the seven gates. The functions will be tested in a copy of the first worksheet.

### Create a stacked signal group

– In the previous section, we created a stacked group of x-axes. Let’s now create a stack of the nine

signals (two inputs and seven outputs) signals that we could represent on the same chart without

overlap. These nine signals are offset copies of the original nine signals and they will finally be charted.

– Cell Q35: “=B35” then copy Q35 right to cell Y35

– Create a series of stacking offsets: Q36: “=0”,

R36: “=offset”, S36: “=2*offset”, T36: “=3*offset”,

U36: “=4*offset” ……….. Y36: “=8*offset”

– Cell Q37: “=B37-Q$36” then copy Q37 to the

right, up to cell Y37

– Copy range [Q37:Y37] down to row 837

### Creating the simplified inverter model:

– Cell D37: “=IF(B37< 0.5,1,0)” then copy (auto fill) cell D37 down to row 837

Creating the simplified AND gate model:

– Cell E37: “=IF(AND(B37>0.5,C37>0.5),1,0)” then copy (Auto Fill) cell E37 down to row 837

Creating the simplified NAND gate model:

– Cell F37: “=IF(AND(B37>0.5,C37>0.5),0,1)” then copy (Auto Fill) cell F37 down to row 837

Creating the simplified OR gate model:

– Cell G37: “=IF(AND(B37<0.5,C37<0.5),0,1)” then copy (Auto Fill) cell G37 down to row 837

Creating the simplified NOR gate model:

– Cell H37: “=IF(AND(B37<0.5,C37<0.5),1,0)” then copy (Auto Fill) cell H37 down to row 837

Creating the simplified XOR gate model:

– Cell I37: “=IF(OR(AND(B37=1,C37=0),AND(B37=0,C37=1)),1,0)” then copy (Auto Fill) cell I37

down to row 837

Creating the simplified XNOR gate model:

– Cell J37: “=IF(OR(AND(B37=1,C37=0),AND(B37=0,C37=1)),0,1)” then copy (Auto Fill) cell J37

down to row 837

Preparing the chart, plotting the stacked x-axes:

– Select range [N39:O64] => Insert => Chart => XY

(Scatter) => select the one with discontinuous lines => finish

– Delete all grid lines and format the vertical axis range to

be between -17 to +2

Series1

– Right click the horizontal axis => Scale => Value (Y) Axis

Crosses at => type -17

– Right click the vertical axis => Scale => Major unit = 100,

Minor unit = 100, Font = 1

0 10 20 30 40 50 60 70 80 90

– Right click chart => Chart Options => Titles = Value (X) time [ns]

Axis = time [ns] => Value (Y) Axis = Voltage

Plotting all the input signals on the same chart:

A

– Right click the chart => Chart Options => Gridlines =>

B

select Value X Axis => Major Gridlines

-Right click the chart => Source Data => Add => Name =>

type A => click in the box “X Values” and then select the

range [A37:A837] in the worksheet => click in the box “Y

Values” then select the range [Q37:Q837] in the worksheet

-Right click the chart => Source Data => Add => Name =>

type B => click in the box “X Values” and then select range

[A37:A837] in the worksheet => click in the box “Y

Values” and then select the range [R37:R837] in the 0 10 20 30 40 50 60 70 80 90

time [ns]

worksheet

Add the first two output traces to the chart: Series1

A

-Right click the chart => Source Data => Add => Name => B

INV

type INV => click in the box “X Values” and then select the

AND

range [A37:A837] in the worksheet => click in the box “Y

Values” then select the range [S37:S837] in the worksheet

– Right click the chart => Source Data => Add => Name =>

type INV => click in the box “X Values” and then select the

range [A37:A837] in the worksheet => click in the box “Y 0 10 20 30 40 50 60 70 80 90

time [ns]

Values” then select the range [T37:T837] in the worksheet

Finish by adding all the output traces to the chart and refining the formatting:

– Using the procedure outlined

A

previously finish by adding all

the output traces to the chart. B

You can format the size and

INV

color of the legend, the

AND

background color of the chart

NAND

and the gridline format.

– Get rid of the “Series1” title OR

from the legend by clicking and

NOR

highlighting it then hitting the

XOR

“Delete” key on the keyboard.

XNOR

– You can also adjust other

aspects of the chart to your 0 10 20 30 40 50 60 70 80 90

time [ns]

preference.

A chart variation: changing the time step size and the period of input signal “B”:

– For the snapshot below I used a time step of 0.15ns and a period of signal B of 30ns (cell C37:

“=0.5*(1+SIGN(SIN((2*PI()*A37/30))))” then auto fill down column C to row 837.

A

B

INV

AND

NAND

OR

NOR

XOR

XNOR

0 20 40 60 80 100 120 140

time [ns]

### A small waveform animation:

Public N As Boolean

Dim p As Double

– Let’s create a small animation by making variable period input signals.

-Replace formula in cell B37 with Sub period_b_variation()

N = Not N

“=0.5*(1+SIGN(SIN((2*PI()*A37/$A$8))))” and the formula in cell C37

Do Until N = False

with: “=0.5*(1+SIGN(SIN((2*PI()*A37/$A$11))))” after which copy the

p = p + 0.1

range B37:C37 down to row 837. DoEvents

[A8] = 10 * (4 + 2 * Sin(p / 10))

– Write the macro to the right and assign it to a button:

DoEvents

[A11] = 10 * (2.2 + 2 * Sin(p / 7))

DoEvents

Loop

End Sub

– As you can see from the

snapshot to the left I assigned a

labeled range (A7:A11) for the

two period values but this is not

absolutely necessary since the

macro will constantly fill cells

A8 and A11 during operation.

– The animation can be started,

paused and restarted from the

same red button labeled

“Animation On/Off”.

to be continued…

Comments are closed, but trackbacks and pingbacks are open.