Constants and Variables

Before getting started on formulas, it’s important to discuss two things: constants and variables.  Most people learned about this in Algebra class, but that was a long time ago, so here’s a refresher.

If it’s a number in a formula, it’s a constant.  1, 5, 37, and 9.3 are all constants.

If it’s in a formula and it’s not a constant, it’s a variable.  Most spreadsheets have two kinds of variables: cell references and named variables.

Cell references are just what they sound like:  they refer to the value in some other cell.  For example, using the variable “a2” would be the same as saying “whatever the value is in cell A2.”

Named variables are also just what they sound like: references to some other cell that has been given a name for user convenience.  For example, “A2” doesn’t tell you much about what the value in the cell represents, but “JulyIncome” gives a much clearer idea, even if it’s the same as far as the computer is concerned.  When creating generic examples I’ll usually use the names “Foo” and “Bar”.

To name a variable in Excel 97,

  1. Select the cell or cells you want the name to refer to.
  2. Click on “Insert”
  3. Click on “Name”
  4. Click on “Define”
  5. Enter a descriptive name that will make sense later in the “Name” box.
  6. Click “okay”

To name a variable in Excel 2007,

  1. Select the cell or cells you want the name to refer to.
  2. Click on the “Formulas” tab
  3. Click on “Define Name” (middle section, top row)
  4. Enter a descriptive name that will make sense later in the “Name” box.
  5. Click “okay”.

To name a variable in Google Docs,

  1. Select the cell or cells you want the name to refer to.
  2. Click on “Edit”
  3. Click on “Named Ranges”
  4. Click on “Define new Range…”
  5. Enter a descriptive name that will make sense later in the “Nickname” box.
  6. Click “Save”
  7. Click “Done”

To name a variable in OpenOffice

  1. Select the cell or cells you want the name to refer to.
  2. Click on “Insert”
  3. Click on “Names”
  4. Click on “Define”
  5. Enter a descriptive name that will make sense later in the “Name” box.
  6. Click “OK”.