Advertisement

Blog

Using Excel as a Design Aid in Lookup Tables

Using the lookup table device in a PSoC left me a little confused with all the options. So I used Excel to help me out.

It is no secret to my legions of followers (Hi, Mom!) that I enjoy working with Cypress’ PSoC architecture especially the PSoC4 and PSoC5 varieties. Let me state up front that this blog is far more about the usage of Excel than about the PSoC, so don’t tune out just yet. I have written quite a bit on the application of Microsoft Excel to different aspects of electronics, often, I hope, with some originality. I have provided a listing here , just in case you wish to validate my claim.

Recently I was working on a project using the PSoC5LP. You can see the schematic of part of the internal configuration if Figure 1. One of the “components” available is a Look Up Table (LUT) that can have up to 5 inputs and 8 outputs.

Figure 1

Figure 1: Layout in PSoC Creator. The 'Configure LUT' window is where you would fill in the outputs of the lookup table. Incidentally, it ireally easy to measure the outputs of the internal devices by routing to external pins.

Figure 1: Layout in PSoC Creator. The “Configure LUT” window is where you would fill in the outputs of the lookup table. Incidentally, it ireally easy to measure the outputs of the internal devices by routing to external pins.

In brief, I am trying to provide two signals that vary with a 3 bit configuration set on the “Control_Reg_1” register. When “Window” signal line is low, I want each output (Trig1 and Trig2) to be independent. If the signal LT1 is low then I want Trig1 output will follow the output of the comparator Comp1, i.e. Trig1 will go high when the signal in input pion Analog1 is greater than the reference generated on VDAC1 on the negative input of the Comp1. When LT1 is high, the output signal will output the inverse of the Comp1 signal. i.e. it will go positive when the input signal is less than the reference on the negative input of the Comp1. Of course the same is true for Trig2, with the “1”s changed to “2”.

When the “Window” signal is set high, one of the analog signals is commoned to both comparators and the LUT becomes a Window detector with the upper window limit set on VDAC1 and the lower window limit on VDAC2. When LT1 is low, Trig1 will go high when the input signal is outside the window and when LT1 is high, Trig1 will go high when the input voltage is within the window.

For all PSoC Creator’s sophistication, when it comes to filling in the LUT it can be quite primitive. All you have is to adjust the number of inputs and outputs and fill in the outputs of the associated input patterns. But you cannot name the pins and you can’t adjust the order to suit your purposes. I tried it twice and got so confused I decided to figure out a better way using Excel.

You can find the completed worksheet “LUT” here. I started out recreating the LUT as you can see in Figure 2.

Figure 2

Figure 2: Initial Layout of the LUT. Note that I added the ability to name inputs and outputs in row 11.

Figure 2: Initial Layout of the LUT. Note that I added the ability to name inputs and outputs in row 11.

There are many tools to speed up creating the auto numbering like entering out7, out6 and then stretching that to out0, but this blog is going to be long enough without adding basics. I started out with the numeric sequence in column B. I then converted it to the hex format in column C using the DEC2HEX formula and similarly I created it in binary in column D using the formula DEC2BIN . You can see an example of that in the formula bar at the top of the screen. Using the binary input, I extracted each bit in the in4 to in0 columns for each row using a formula similar to =MID($D18,3,1) , which appears in cell I18. I also added all the outputs and I then hid them by first clicking and dragging from L to Q in the row at the top (this is called blocking columns L to Q), right clicking and selecting Hide . We will use hiding to great effect in this example.

The first confusion in entering data is that the “Window” input (in0) changes every alternate line and logically we want to consider it as a group. The quickest way to simplify this is purely visual by hiding each line where in0 has the value 1. Click on row 14 in the very leftmost region. Then holding down the <Shift > key click on each alternate line so that each selected line is highlighted. Then with the mouse hovering over any one of the selections, right click and select Hide . You will see the output with the selected rows missing, resembling Figure 3. It will also be much easier if we deal with just the output “TRIG1” and so I also hid columns I, J, K and S.

Figure 3

Figure 3: Working with a channel 1 in the dual channel mode result in a much smaller and more logical table

Figure 3: Working with a channel 1 in the dual channel mode result in a much smaller and more logical table

It is then pretty easy to fill in the outputs for “TRIG1”. I added a description to help for future debugging efforts. It is pretty easy to copy the text in the “Utility” column. Excel allows a bulk copy by selecting one cell, <Ctrl> + C , selecting a number of cells and pasting to them. Beware of using that in this example since it pastes into the hidden cells as well. Not a problem her, but it can be a problem in future steps.

In order to deal with the “TRIG2”output, unhide the columns from F to T (block them and the right click and select Unhide ). Then hide the columns G,K and I through R so the input is like Figure 4.

Figure 4

Figure 4: Dealing with the second output 'TRIG2'. The initial hiding still leaves a sub-optimal arrangement, so we use Excel's sort facility to help.

Figure 4: Dealing with the second output “TRIG2”. The initial hiding still leaves a sub-optimal arrangement, so we use Excel's sort facility to help.

You could go ahead and fill in the “TRIG2” output, but it still could be quite confusing given the way the two inputs bob around. We can use Excel’s sort facility to help us out. I had enough foresight to leave a blank column in F. I recreated the binary pattern from the “COMP2” and “LT2” columns by concatenating the cells in column I and column J using the formula =I4 & J4 (as it is in cell F4) and copying this to the cells column F. Again, don’t use the bulk copy, but you can use the multiple select using the <Shift > key in conjunction with the mouse click. Now block B13: S30, and on the Excel ribbon, go to the Data tab. Click on Sort and the window you see in Figure 4 will pop up. Sort by Column F. You may get a warning as in figure 5- just select the option as indicated.

Figure 5

Figure 5: Make the text look like a number.

Figure 5: Make the text look like a number.

Figure 6

Figure 6: Now the second channel is much easier to handle.

Figure 6: Now the second channel is much easier to handle.

The result in Figure 6 is much more conducive to filling in the “TRIG2” output. I added the Ch2 descriptors in the “Utility” column to the text that was there from previously.

Now delete the contents of Column F. Re-block the table B13:S30. Repeat the sort, but Sort by Column B to restore the original order. Now unblock all the rows and columns. Select the alternate rows where the “Window” input is zero and hide them as well as columns L to Q. Now it is possible to go through all the combinations, but we can use Excel to help us.

Figure 7

Figure 7: Using Excel to find which combination meets the criteria for outside the window. 'TRIG2' is not used and set to 0 in all cases.

Figure 7: Using Excel to find which combination meets the criteria for outside the window. “TRIG2” is not used and set to 0 in all cases.

“LT1” is used to indicate which window mode will be used. When LT1 is low, (outside window mode) if COMP1 is high AND COMP2 is low then TRIG 1 should be high. Again I will use the column F for helpful calculations. I used nested Ifs to find the desired condition. The formula will return a blank in any condition where the formula doesn’t return a true value. The formula in cell F14 is seen in the formula bar at the top of Figure 7 and is copied to the whole column with automatic cell reference adjustments. The formula displays a 1 in only two cells and the corresponding cells in column R can be updated to a 1 as well. A comment is added in the “Utility” cells.

Figure 8

Figure 8: Looking for the conditions to indicate the signal is inside the window

Figure 8: Looking for the conditions to indicate the signal is inside the window

First delete all the formulae in column F. Similarly add a formula to find the conditions for the signal inside the window: =IF (H14<>”1”,””,(IF(G14<>”1”,””,(IF(I14<>”0”,””,1))))) . The associated cells in “TRIG1” are manually updated and change the other blank cells in “TRIG1” to “0”. Then delete all the formulae in column F, again.

Figure 9

Figure 9: The finished article. Please note I have forgotten to unhide the top row corresponding to a '0' input.

Figure 9: The finished article. Please note I have forgotten to unhide the top row corresponding to a “0” input.

(I wrote this blog “on the fly” alongside the work as I did it in order to capture the screenshots. Now that I review it, it may be possible to have done the whole thing using this last approach. The method is very similar to the one we learned to realize a function with gates, by analysing the “1” outputs on a truth table. This approach holds some potential for a future investigation, but in the present case you may have missed all the nifty tricks that Excel possesses.)

Unhide all the rows and you are left with Figure 9 (minus the shading). Unfortunately it is not possible to cut and paste this back into the LUT in Creator as shown in Figure 1, so you are left doing it manually. But it is much less confusing than setting it up from scratch.

Although I have not come up with a way to add the benefits of automation to this and turn it into a standalone tool, I do believe the approach has some merit. I hope you do too.

6 comments on “Using Excel as a Design Aid in Lookup Tables

  1. cbelting
    November 16, 2016

    The link to Figure 9 is broken. Would you please restore it?

  2. Steve Taranovich
    November 16, 2016

    @cbelting—Figure 9 is restored—thank you for noticing—I appreciate it

  3. X ray tech
    January 16, 2017

    If the support from microsoft office would would have been better, it would have been such a great product to use. But unfortunately the live support is not active.

  4. antedeluvian
    January 17, 2017

    @X ray tech

    If the support from microsoft office would would have been better, it would have been such a great product to use. But unfortunately the live support is not active.

    It is true that good tech support can enhance user experience, but I don't think it modifies the actual quality of the product.

    I have contacted the tech support at many semiconductor manufacturers only to discover that there is only a chat room and often you know more about the product than the person answering. Yet the devices are great.

    When I was writing my book “Excel by Example“, Microsoft did provide live technical support (for all I know they still do). They had a pay service that was ranked as “individual” and “corporate” and I think there was a third level above that as well.  I was researching an issue with the Solver utility in Excel and decided that I could afford the cost for individual level. Well, when I asked my question I was told that it was far too advanced for a the individual level. So I then asked if I paid extra and upped the level, if they could in fact answer the question. They had no idea if they could in fact answer the question at all. Again this goes to show that tech support is not always great even if it is live. I also find that Excel's Help utility is far less comprehensive than it used to be, now relying on web searches to provide the answers.

    It doesn't justify Microsoft's approach to tech support, but Excel and Word are so widely used that there are a multitude of places that you can ask a question and get a great answer. They are also a fortune of other resources that can be used to help and inspire.

    What other general purpose utility could you be using? Which one will provide you with the level of tech support you require?

  5. planetanaIog
    January 18, 2017

    The image is working fine for me.

  6. antedeluvian
    March 15, 2017

    Unfortunately Dropbox has chnaged their implementation of public files. This has affected many, many instances where I have linked to my files. I am trying to update the links as I come across them. Dropbox will ask you to register in order to copy the file or open the file (you can only open the file once you log in)

    In this blog this is the revised link to the LUT worksheet

    I also have updated the link in the following statement
    I have written quite a bit on the application of Microsoft Excel to different aspects of electronics, often, I hope, with some originality. I have provided a listing here , just in case you wish to validate my claim .”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.