Excel Formula Basics
Overdrive Pro and Microvellum Toolbox use Microsoft Excel's syntax for formulas which, simply stated, is this: Any cell's value that starts with an equal sign (=) is considered to be a formula. To keep track of where you've stored your information, each cell has an address. The first cell in a spreadsheet is given the address A1, which means column A row 1. So you can identify any cell's address by determining its column and row. To explain how a formula might be used to calculate some useful information, we will suppose you want to determine the inside width of a cabinet. The overall outside width is 36" and the thickness of both cabinet sides is ¾". If we enter 36 in cell A1 and 0.75 in cell A2, we only need to enter a mathematical formula in cell A3 to get our answer. The formula could be written as follows: =A1-A2*2
The magic of spreadsheets is easily understood when you change the number in cell A1 or A2. Since the formula in A3 is based on the values of these two cells, the formula evaluates to something different each time one of the two cells are changed. Change A1 to 24 and A3 instantly changes to 22½ all by itself. Change A2 to 0.05 and A3 changes to 23. If you put a value into A1 or A2 that doesn't make sense to use with multiplication or subtraction, your computer doesn't blow up. But rather, it politely returns an error message to alert you to the fact that you've been staring at your computer screen too long and should probably take a break. Of course the example of calculating the inside width of a cabinet is extremely simple; in fact, you can perform the math in your head almost as fast as your computer. But since each spreadsheet contains 256 columns and 65,536 rows that you can fill with numbers and formulas that all relate to one another, it doesn't take long before you can have a spreadsheet that can make hundreds of valuable calculations instantly. Going back to the formula we used to calculate the inside width of a cabinet, =A1-A2*2, I'm sure you would agree that even though the formula is extremely simple, it's not very easy to decipher at first glance. And, if you had hundreds of other formulas in your spreadsheet, it would be even more difficult to recognize that this particular formula is the one that calculates the inside width of a cabinet. To make things easier, we will assign our own names to cells A1 and A2. We’ll call cell A1 "CabinetWidth", and cell A2 "SideThickness". Now we can change the formula in cell A3 to read: =CabinetWidth-SideThickness*2. As far as the computer is concerned, both formulas, =CabinetWidth-SideThickness*2 and =A1-A2*2, mean exactly the same thing. The first one is just easier for us humans to understand. To make things even easier on us, let's name cell A3 "InsideCabinetWidth". Now, anytime we need to know what the inside width of a cabinet is, we just need to find an empty cell and enter the formula: =InsideCabinetWidth. The answer is immediately provided. If you don't look at the spreadsheet for six months, =InsideCabinetWidth will still mean something to you; even though you probably forgot that this formula was entered in cell A3 and is based on information in cells A1 and A2. Excel provides a quick and easy way to give names to cells in the spreadsheet. You can enter names into cells in a column and ask Excel to “define” the cell to the right with the same name as the cell from the left hand column. Later you'll see that Overdrive Pro uses this
functionality to allow you to create something we call prompts for a product.
A prompt allows the user to enter
values for width, height, depth, toe kick height, etc. The prompts are nothing more than named
cells in a spreadsheet. This provides
complete flexibility over product design. |
|||||
|
|
