Modeling Flip-Flops and Counters in EXCEL

In a recent article I designed an entire ALU (Arithmetic Logic Unit), the heart of a Computer CPU, in EXCEL: Designing Digital Logic Circuits with EXCEL

Everything there was static logic that can be defined by truth tables. This is a follow up article where I explore dynamic, clocked digital circuits like flip-flops and counters. They are much more difficult to model in EXCEL, because they involve timing, electrical race issues, feedback, and in EXCEL, iteration.

There are two issues here. The first is that these circuits, even in real life, not EXCEL, are unstable unless the precise time it takes for signals to propagate through the circuit are taken into account. The second problem is that EXCEL is designed to avoid circular logic, like feedback. It does have an iteration feature, which we need to turn on, but even so, it is pretty inflexible about how it deals with the order in which things happen.

As with my previous article, I am using a relatively old version of EXCEL (2010). It may be that newer versions handle all this somewhat better. In all cases, however, I believe it is necessary to enable iterative calculation in EXCEL. It is under File Menu, then Formulas - click on it to enable; otherwise you get circular logic errors!

The Flip-Flop

Our first task is to get a working flip-flop. Most simple flip flops don't work in EXCEL. They simply don't set and reset, or latch and unlatch, the way they are supposed to, because EXCEL doesn't model the propagation delays that make these circuits behave as they do in real life.

But I found one flip flop that does work: the JK Master Slave Flip-Flop. This is a circuit that was specifically designed to be stable and avoid the problems caused by race conditions. A Master-Slave Flip-Flop avoids race condition problems by dividing the clock signal into two phases. The first phase controls the master latch, while the second phase controls the slave latch. This ensures that the two latches do not receive feedback from each other, and allows it to work in EXCEL!

Here is the circuit for the JK Master Slave Flip-Flop:

And here it is in EXCEL, laid out just like the circuit above:

The EXCEL file is MasterSlaveFlipFlop.xlsx. If you open the EXCEL file, you can check out the flip flop. Change the clock to 1 and then back to 0 to toggle the fip-flop.

Even with this highly stable flip-flop, I found some surprises implementing it in EXCEL. The gates need to be left in tack. For example, cell G4 = E5, and cell G6 = H9. Cell H5 = if(G4*G6=1, 0, 1). I should be able to rewrite cell H5 as =if(E5*H9=1, 0, 1) and eliminate G4 and G6. But it doesn't work. Even with this stable design, propagation still comes into play.

So I need all the cells that are in our flip-flop, but I still can clean it up and make it more compact and user friendly.

This is file Compact_JK_flipFlop.xlsx. It still functions just like the first version.

Asynchronous 4 Bit Counter

So now we can proceed to build a counter from our latest flip-flop. The circuit is:

And the EXCEL file is 4BitAsynchronousCounter.xlsx.

This EXCEL file works just like the single flip flop. Set the clock to 1 and back to 0 and you will see the counter change to 1, etc. It counts up to 15 and then starts over. I have put the resulting count at the top of the page both in binary and decimal.

An Alternative Strategy

So we were able to build a JK Master Slave Flip-Flop and an asynchronous counter from it. However, I couldn't make a synchronous counter work, and I couldn't get any other flip-flops to work other than the master slave.

So we will now look at one other way to model flip-flops and time based clocked circuits in EXCEL. In this approach, time goes down the spreadsheet, and dependencies between cells are limited to the current row and the one above it. Each row or line on the spreadsheet represents the state of the system after one additional clock cycle.

This approach in theory always works, as it eliminates timing issues and circular dependencies. However, it is much less fun to watch than live logic interactions.

Our 4 bit counter this time is a synchronous counter built with a much simpler D-type flip-flop. This D-type flip-flop doesn't work at all when we try to model it in EXCEL with live logic, like we did with the master slave flip-flop.

As a side note - in real life, this synchronous counter can operate at much higher clock rates than the asynchronous one we build from JK master-slave flip-flops, due to much shorter propagation delays.

Here is the circuit for a D-type flip-flop:

The D flip-flop is basically a latch. A clock pulse takes the status of D and latches it into the output.

Our new synchronous 4 bit counter using 4 D flip-flops looks like this:

I have labeled the active nodes of this counter A through J. These labels are the column designators in EXCEL The rows in our spreadsheet represent the different states of each node after one more clock cycle. Note that the clock itself is not a node, as its state simply goes to 1 and back to 0 with each new row.

And finally, our EXCEL spreadsheet version, file Synchronous4BitCounter.xlsx:

Row 3 sets up the initial condition of our counter, then row 4 show the new state of each node after 1 clock cycle. All the other rows are simply copies of row 4, as they step through additional clock cycles.

We can see the behavior of each of the flip-flops over 32 full clock cycles. You can see the output of the counters (columns A-D) in green, and I converted it to decimal in column L.

This approach is perhaps a more tedious process than just constructing the actual circuit in EXCEL, but it is much more likely to work, as it sidesteps all the timing issues associated with live logic, and it does get easier with practice.

I will leave you with one more example, another synchronous counter, this time with a T-flip-flop or toggle flip=flop.

As with the previous counter, I have labeled the nodes with the columns in EXCEL, A though H. The EXCEL file is SynchrTCounter.xlsx. One interesting thing with this file was that I could super easily change it from a 4 bit counter to an 8 bit counter, but simply dragging the 4 Q output sideways to create 8, and drag the gate outputs sideways to create 8. This EXCEL file is 8bitSynchrTCounter.xlsx, and it counts all the way from 0 to 255.