|
Labels and Names
Labels and names are really useful for coding formulas. Labels are used to automatically point to a row or column. Names are more flexible but also more tedious setup, allowing either absolute or relative referencing through the Defined Names menu.
Defined Names
Defined Names let you designate values for the entire workbook, and is great for passing constants between worksheets. There are several ways to define the names; you can start with the labels next to the values, select the label and the value cells, and Insert/Name/Create; or you can Insert/Name/Define and put in your own name and reference. With the reference, you can give a name to an array (for example, a table you'll be using for lookup), a fixed range or cell (Sheet1!$B$16), or a relative range or cell (Sheet1!B$16 will look at cell 16 in whichever column is currently asking for the value). Better still, you can put constants in the reference box directly (e.g. k_copper refers to 390) or even a formula. For more on this topic, including how to make the range expand and contract with your data, and how to make the same named range work for different worksheets within the same workbook, get the book Excel Hacks, by David and Raina Hawley. I picked it up at my local large chain bookstore, so it shouldn't be too hard to find (and no, I'm not getting a commission!).
Labels in formulas
Labels are a terrific tool to write and debug spreadsheet calculations. Labels work for both columns and rows. I tend to use them mainly in rows, so that's what I'll describe here, but they'll even do intersections if that's what you want.
To enable the feature (it's not the default), in Excel go to Tools/Options; on the calculation tab, check the "Accept Labels in Formulas" box. (I’m still using the 2003 version of Excel; I suspect this isn’t available in the new versions.) Note that these labels work only within the same worksheet of a workbook - not across multiple sheets (use Defined Names for that).
Set up your calculation by using variable names in the first column. You have to be a bit choosy on the label that will be the variable name. These variable names should be descriptive to you, but shouldn't look like a cell reference to Excel. For example, "T1" is not a good choice -- it confuses the program. "Temp1" is fine, though. Labels aren't case sensitive, so you can use caps in them for clarity but omit them when writing formulas. Spaces are usually okay, but can confuse things too; if that happens, put the label in single quotes when writing the formula. For the sake of an example, set up the following labels, one per row:
Area
resistivity
power
R_TIM
TempRise
Next, put numerical values in the column next to the labels where needed -- for the example, enter values for area, resistivity, and power. (Be careful to use consistent units!)
Now you're ready to write the formulas. You can pretty much work the way you're used to, except that you don't need to insert the cell reference; just type the label instead. In the example, in the cell next to the label R_TIM type =resistivity/area. Then for the temperature rise, enter the formula =R_TIM*power. This way, you can easily debug your formulas without wondering what quantity you had entered in cell B4.
For a two-dimensional example of labels see the thermal networks page.
|
