THE MAGAZINE FOR COMPUTER APPLICATIONS
Circuit Cellar Online offers articles illustrating creative solutions
and unique applications through complete projects, practical
tutorials, and useful design techniques.

DO YOU EXCEL IN ELECTRONICS?

Part 1: Learning the Basics of Excel
by Aubrey Kagan

Start ý Data Acquisition and Analysis ý Autofill ý Copying Formulas ý Relative and Absolute ý Naming Cells ý Determining the Cost ý Sorting ý Using Look Up ý Figuring the Total ý Sources and PDF

DETERMINING THE COST

Having made disparaging remarks about books on Excel, let me eat some of my words by presenting an application that could come out of their pages. Most of us have used Excel to create a bill of materials and then sum the number of components we use per board, enter the prices, and get a cost for the board. There really is a problem, though. The price of components varies according to quantity purchased, and as a result, production cost of the unit will vary as well. I will explain how to use look-up tables to account for this.

Look-up tables have been around since the early days of spreadsheet development. I developed the model that Iým going to show you back in 1983 on an Osborne 1 using Supercalc. How I miss the 3ý screen and the two 180-KB floppy drives. Not!

A cell in Excel can hold three entitiesýa string, a numeric value, or a formula or function that will result in a text or number being displayed after the calculation is completed (unless a fault is detected). When an alphanumeric character(s) is entered, Excel interprets it as a string unless the first character is = or @. In either case, Excel will then attempt to interpret the input as a formula or a built-in function.

Any pure numerical entry will be taken as a number. For instance, entering 8751 as a microcomputer component in a cell will be interpreted as the integer 8751. To persuade Excel to treat this as a string, enter an apostrophe before the number (ý8751). By default, Excel will align a string on the left and a number on the right. Of course, it is possible to select any cell or group and align them in one of a multitude of ways after entry.

I have created a fictitious bill of materials of a simple product for you to see in Example2a (download Excel files), see Photo 12.

(click here to enlarge)

Photo 12ýThis is a sample of an initial bill of materials (BOM) for a simple product. The components can be entered in any order.

PREVIOUS • NEXT