Advertisement

Blog

Using Data Tables in Excel

I started writing a series of blogs on the use of Excel spreadsheets for circuit design on the now-defunct Microcontroller Central. Those blogs, though separate from this blog and future ones that I will write on Excel, will create a more complete picture with their presence, so Planet Analog has added them to its database. (They can be found in the Related Posts list at the bottom of this blog.)

In this blog on Excel, I would like to discuss how the Data Table feature can help with the design process. Let's consider a 555 timer configured as an astable multivibrator, as shown in Figure 1.

Figure 1

555 configured as an astable multivibrator with a frequency given by f = 1.44/((RA+2RB)•C).The mark-to-space ratio is given by RB/(RA+RB).

555 configured as an astable multivibrator with a frequency given by f = 1.44/((RA+2RB)•C).
The mark-to-space ratio is given by RB/(RA+RB).

The Data Table function can handle one or two input variables and can be presented vertically or horizontally in the worksheet. I will discuss only the vertical presentation in this post. The translation to horizontal format is straightforward, and I will leave it to the user as an exercise (as my professors used to say).

Let's pose a scenario: We want to configure the 555 to work at up to 100 kHz with a potentiometer installed as RA. We will choose RB as 4.75kΩ and C as 0.1μF. Set up the worksheet as in Figure 2. The values in column C are possible pot values, and the result of the frequency calculation appears in cell D4. The relative positions are important. The column containing the values to be considered (column C) must start at and be to the right of the cell whose value it is going to replace (cell B5). The formula to be evaluated should be one row up and one column to the right (cell D4).

Figure 2

Initial worksheet setup. Note the formula for the frequency in cell D4 shown in the formula bar (fx at the top). Note also that cell C3 is formatted for two lines thusly: Right click on the cell, and then select Format Cells | Alignment and choose Wrap text under the Text control.

Initial worksheet setup. Note the formula for the frequency in cell D4 shown in the formula bar (fx at the top). Note also that cell C3 is formatted for two lines thusly: Right click on the cell, and then select Format Cells | Alignment and choose Wrap text under the Text control.

Now block (using click and drag) the area containing the variable, the calculation, and where the results are to be placed. In other words, block C4 to D11. Then click on the Data tab on the Excel ribbon followed by What-If Analysis in the Data Tools subdivision. Then select Data Tables. In the resulting dialogue box, enter the source variable that will be analyzed. You should be looking at something like Figure 3.

Figure 3

Start of the Data Table analysis. You could enter B5 in the Column cell input manually, or you can point to the cell you want by clicking on the worksheet icon on the right. This approach adds the $ symbols to fix the cell without allowing for transposition in copying. I don't think it makes any difference here.

Start of the Data Table analysis. You could enter B5 in the Column cell input manually, or you can point to the cell you want by clicking on the worksheet icon on the right. This approach adds the $ symbols to fix the cell without allowing for transposition in copying. I don't think it makes any difference here.

Click on OK, and you should see the result in Figure 4.

Figure 4

First step of the analysis complete. The calculated frequency (minimum)is paired with possible pot values (maximum).

First step of the analysis complete. The calculated frequency (minimum)
is paired with possible pot values (maximum).

From the results in cells D5 to D11, we can see we get nowhere near the desired 100 kHz. If you modify the values in cell B6 (RB) and cell B7 (C), you can see the result change dynamically — something that was not possible in the Goal Seek and Scenario approaches we have seen in previous blogs. If you click on the result cells, you will see that Excel has put rather strange formulas here. These are known as array formulas. (This tidbit is just for future reference and will be on the test.) Changing the entry for C to 1nF (0.000000001) will bring the frequency range into focus. I found this by trial and error.

It is possible to add additional calculations. For instance, let's say we wanted to see how the mark-space ratio performed with these values. Let's add a new column for the mark space calculation. This is shown in Figure 5.

Figure 5

New column for mark/space, with the formula in the formula bar. You could format the column as a percentage or limit the number of digits.

New column for mark/space, with the formula in the formula bar. You could format the column as a percentage or limit the number of digits.

We then repeat the data table process with a minor modification of the data area, as you can see in Figure 6.

Figure 6

Analysis of two formulas. The results of the original analysis in column D will be overwritten,though this is not an issue here.

Analysis of two formulas. The results of the original analysis in column D will be overwritten,
though this is not an issue here.

The Data Table analysis can be stretched to a second dimension. Let's see how the frequency will change as we manipulate both RA and RB. Let's rearrange the data so that the frequency calculation is in cell C23, with the first variable (RA) in a column beneath it and the second variable (RB) stretching out to the right of it, as you can see in Figure 7.

Figure 7

A two-dimensional Data Table.

A two-dimensional Data Table.

Block the formula, input variables, and output area, as shown in Figure 8. Then, as before, click on the Data tab on the Excel ribbon, followed by What-If Analysis in the Data Tools subdivision, and select Data Tables. Select the row and column input cell — the cell referenced in the formula that will be evaluated according to the input data into the row or column.

Figure 8

Two-variable setup.

Two-variable setup.

Click on OK to complete. For completeness, I have shown the result in Figure 9.

Figure 9

I have formatted the results for integers and colored them to help with the visibility.

I have formatted the results for integers and colored them to help with the visibility.

Since you can change any of the input variables and see the immediate result, you could build this into a generic model and then twiddle the values to get the desired results. It's not particularly elegant, but maybe it will help in some instances. Knowledge empowers. You could also enhance the performance by adding features like conditionally formatting the cells in the result to indicate when a condition (like the desired frequency) is met. There may be more on conditional formatting in a future blog.

The ideas I have presented are a little contrived for two reasons. First, pulling a good example out of the air is not always easy. Second, I wanted to use this to show how much more powerful the Solver tool is. That tool will be discussed in my next blog on Excel in a few weeks.

Related posts:

18 comments on “Using Data Tables in Excel

  1. RedDerek
    October 2, 2013

    I use Excel quite often. I have one huge spreadsheet on resistors where I can quickly caluclate the nearest 5%, 1%, etc value for a resistor divider, parallel resistors, etc.

    One of the things I could never get Excel to do nicely is present engineering notation. The 'C' value forces one to count the zeros. But if it showed 100e-9 or 10e-6, it would be much easier to tell if it is nano- or micro- Farads.

    Got a simple solution for this?

  2. antedeluvian
    October 2, 2013

    RedDerek

    The 'C' value forces one to count the zeros.

    I am not entirely surre what you mean by this.

    One of the things I could never get Excel to do nicely is present engineering notation.

    When I did this in my book, I did it all using VBA. The determination of the range (resistors in this case) ohms, Kohms, Mohms was the first thing that was determined, prior in fact to the value lookup. In this series on Planet Analog I am trying to stay away from VBA, so I have been trying to think of ways. One that spirngs to mind is the IF formula. It can be nested so you could have something like:

    IF(cellValue<10^-9,ggg,(IF (cellValue<10^-6,hhh,(IF (cellValue<10^-3.......

    ggg would be the formatting to present the data in pF format (multiply the number by 10^9 and append “pF”), hhh in nF format (multiply the number by 10^6 and append “nF”) and so on. Tedious to create the first time, but once it is done you could copy and paste to your heart's content. Do it and you could publish it as a design idea in EDN.

    I hope this is something you could use. I will try to come up with something else.

  3. antedeluvian
    October 2, 2013

    RedDerek

    One of the things I could never get Excel to do nicely is present engineering notation

    Here's a thought, and I haven't had time to play with the idea throughly. If you use the IF formula within a conditional format on the cell, and in the conditional format set the format to “Custom” and  “##0.0E+0” where the number of # determines the number of characters before the decimal point (the IF clause makes sure that the exponent is right). You could even coulour the cell accordingly. 

    Unfortunately I don't think you can put multiple conditional formats on a cell or you could get rid of the IF statement. I will look further into that.

     

    EDIT

    Apparently you CAN use multiple conditional format statements on a single cell so you won't need to use an IF statement. As I recall when copying a cell (copy/ paste special/all) the conditional formatting goes along with it.

  4. eafpres
    October 2, 2013

    I use Excel for everything.  Yesterday I was doing decimal to binary and hex conversions then doing XORs (had to do with cryptography).  There are functions in VBA that are not in the worksheet functions; I'm not a quick with VBA so I typically generate them myself.  Excel 2010 allows some very long formulas, so nested ifs can generate almost anything.  I used that to do the decimal to binary, outputting the binary digits into adjacent columns, then using a text formula to combine them for display.

    Conditional formatting I have found to be buggy in Excel 2010.  It is extremely powerful, but sometimes does odd things.  One interesting project for the readers her is to generate a thermal map of a 2-D dataset.  You can do all that with conditional formatting and getting the column widths right to have a decent aspect ratio in the image.

    If I wanted some particular engineering format and couldn't get it with a number format (those are highly customizable) I would do as you say and use a nested if statement to do it.

    Thanks for the interesting material on the table calculations.

  5. Davidled
    October 2, 2013

    Simply, once excel is in the market, I use less paper to make engineering report.  Eventually, excel might replace paper in the every industry sector. For example, excel is very power tool in order to make the data format with any color and fonts including automatic chart.  I think that Lotus123 is the original s/w of excel.

  6. samicksha
    October 3, 2013

    You are right eafpres,many calculations in Excel are executed using VBA, but i guess there is an additional issue with accuracy of VBA, which varies with variable type and user-requested precision.

  7. antedeluvian
    October 3, 2013

    Got a simple solution for this?

    Actually it does seem to be very simple. Simply format the cell (right click the cell, Format Cells | Custom    and then scroll down to and select ##0.0E+0

     

    The cells below all have this formatting and each cell has a value different by a factor of 10 greater than the cell to its left.

    8.2E-12 82.0E-12 820.0E-12 8.2E-9 82.0E-9 820.0E-9 8.2E-6 82.0E-6 820.0E-6 8.2E-3
  8. RedDerek
    October 3, 2013

    @Antedeluvian – That format works perfectly. I remember searching for the answer years ago, but never found it. It works for 2003 version and up. Now, can you guide me how to make that into a button in the top format menu?

  9. antedeluvian
    October 3, 2013

    RedDerek

     Now, can you guide me how to make that into a button in the top format menu?

    First record a macro as you format a cell. That is:

    select any cell, click Developer | Record Macro . In the window that pops up give the macro a name and if you like a hot key combination (better than a button?) and I would reccomend saving it in “Personal Macro Workbook ” so that you can get it in any workbook. Add a description if you want and click on OK .

    Format the cell as before- right click on the cell etc. When done click on the Stop Recording

     Any cell you select and run the hot key combination, or if you run the macro directly will format the cell correctly.

    If you still want to, you can add a macro as a button the the ribbon by right clicking on the ribbon and customizing. Ir's been a while since I have tried (since before the ribbon) so I'll let you get on with it on your own.

     

  10. Vishal Prajapati
    October 4, 2013

    I was never using Excel to solve engineering problem. In my last project when I had to configure the Gas Gauging IC from Maxim, the excel was proved to be game changer. I would have never got down to the right slopes for the battery capacity temperature degrade curves. Then I understood the value of Excel untill then I was thinking it as a tool for economincs people only.

  11. antedeluvian
    October 4, 2013

    Vishal

    Then I understood the value of Excel untill then I was thinking it as a tool for economincs people only.

    You are in for a very pleasant surprise. Excel can be used for many creative things and some companies seem to ebrace it for their tools- Cypress for instance has some great examples. To get an idea of what you can use Excel for, perhaps you could browse the table of contents of my book “Excel by Example: A Microsoft Excel Cookbook for Electronics Engineers” on Amazon. If you can find you way around the Design Ideas section of EDN and Electornic Design (and it appears to be increasingly difficult) there are many ideas there. Here are some that may help give you some more ideas (mostly by yours truly, since I knew what to look for)

    Use Excel To Calculate A-D Level-Shifter Resistor Values

    A better approach to designing an RTD interface with a spreadsheet, based on

    Design an RTD interface with a spreadsheet

    Use Excel To Develop A Traceability Matrix

    Program “excelerates” microcomputer-I/O allocation

    and if you can find mydesign ideas from EDN from April 11 2002 “Tricks improve on Excel LCD Intitialization” based on a brilliant idea from Alberto Ricci Bitti in the EDN September 20, 2001 “Excel Offers Painless LCD Initialization”. Mr Ricci Bitti's design idea motivated the approach in my book (as well as a whole chapter in it) and all of the above.

     

     

     

     

     

  12. Netcrawl
    October 4, 2013

    @Daej you need to explore Excel, Engineering typically use software like MATLAB during their design works, most engineers only have Excel for crunching numbers, pure computational tools. Excel is often seen as a business tool. It is a powerful and versatile tool that can be used for a wide variety of engineering and numerical analysis, it featured so many capabilities some of these are hidden deep inside Excel.

  13. Netcrawl
    October 4, 2013

    @Vishaj Yes it is  a tool for engineering problem, pretty good in crunching numbers, there so many features some of them are hidden deep inside Excel. I'm not an advanced users but I do know their capability, a friend introduce this software, he said its quite powerful, capable of anything.

    Here some good links about How to Start Microsoft EXCEL for Engineers

    http://www.emagenit.com/eae_beg_vba_outline.htm

     

     

  14. Netcrawl
    October 4, 2013

    Yes you're right VBA stands for Visual Basic for Applications, its Excel built-in programming environment. This one has features designed for engineering analysis and modelling and they're pretty good. You can create full featured graphical interfaces and interactive content, and just one thing VBA is an advanced programming tools designed for building Windows-based applications. To better understand how VBA works you need to learn Microsoft VB platform. It quite easy and user friendly, I'll been using this one since college, VBA is awesome.

  15. Netcrawl
    October 4, 2013

    It's almost perfect, fit for any engineering calculations. I'll been using Excel for years, calling upon the powerful VBA programming features in Excel enables me to leverage an infinite array of “hidden” possibilities, these are features hidden deep inside EXCEL. I been developed projects over the years, so outstanding design concepts are fairly intuitive; this enables me to produce reliable solutions efficiently.So what exactly we got here? You gain the benefit of much of my “real world” experience developing everything engineering solutions to a much complex analysis and modeling works. @RedDerek I think you need to explore more, try to get some online tips, check for Option at the menu bar.

  16. Vishal Prajapati
    October 5, 2013

    Wow, Thanks for the details and the links. I never thought the power of Excel would be so much that one can create a whole book on just using Excel for the Engineering…!

  17. Vishal Prajapati
    October 5, 2013

    @Netcrawl, your link is helpful. Thanks. I once had seen someone created cell's background color to create a pixel of the image. When we zoom out we could find a complete image. Utility wise not very useful but creative…

  18. yalanand
    October 27, 2013

    Data tables are portion of a set of instructions that are named what-if analysis apparatuses. When you usage data tables, you are then doing what-if analysis. For instance, you could use a data table to vary the awareness rate and term length that is used in a loan to control possible monthly expense amounts.

Leave a Reply

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