Spreadsheets: using names in formulas

The tutor shows an example of naming items in a spreadsheet for use in a formula.

This post focuses on Microsoft Excel; hopefully I’ll do a similar one for a generic spreadsheet soon.

Let’s imagine Jones faces two tax rates: one for capital gains (15%) and the other for working (26%). Furthermore, they’re both flat rates: no special deductions.

A spreadsheet can easily be created to handle Jones’s situation. For the user’s convenience, they might want to type Capital Gains and Work Income at the tops of two columns, with a few columns in between for easy reading.

Let’s imagine “Capital Gains” is in cell A1. Then, depending on how many entries the user might expect, they can select the range (by dragging the mouse) from, for instance, A3 to A20.

Very conveniently, that selected range can be named by right-clicking it (while still selected), then clicking Name a Range… from the pop-up menu. Name a Range… appears near the bottom of the menu. When you click it, it provides a text box in which to type the name. Just type the name and hit enter: for the case of Jones, the range from A3 to A20 might be called Capital_Gains. (My understanding is that you can’t have a space in a name, so I use an underscore.)

Now let’s imagine Work Income is typed in C1. The range from C3 to C20 (denoted C3:C20) can be selected by dragging the mouse. Next, the user can right-click, then click Name a Range…. Of course, you might call it Work_Income.

In the cells A3:A20 the user can enter the various amounts that are capital gains. C3:C20 will contain entries of work income.

In Jones’s case, the formula for tax owed is 0.15*sum(Capital_Gains) + 0.26*sum(Work_Income). You can type that formula, starting with the = sign, in any unused cell. The tax owed will appear after you leave that cell.

The fact that you can name cells, as well as ranges of cells, really boosts the spreadsheet’s power: using names in a formula makes clear what’s being done.

I’ll be talking more about using spreadsheets:)

Source:

Microsoft Excel User’s Guide. Microsoft: 1993-94.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Tagged with: , , , ,

Leave a Reply