Excel 2003: Anatomy of Formula

Excel 2003: Anatomy of Formula

To understand formulas, you’ll want to think back to your days in high school Algebra and the lesson on “order of operations.” Remember when your Algebra teacher said, “I promise, you will need this knowledge again when you’re an adult.” At which point, you turned to your friends and said, “Yeah, right.”

Well, start writing that letter of apology to your old Algebra teacher – you’re about to apply that knowledge again.

But first, let’s look at common calculation symbols.

All formulas MUST start with an equal sign (=). And, Excel follows standard order of operations rules which state:

• Multiplication and Division are calculated first

•
• Addition and Subtraction are calculated second

•
• Anything in parenthesis is calculated first.

•
• All things being equal, Excel calculates from left to right.

Let’s look at an example. We’ll use the same numbers but arrange them in a couple of different ways to achieve different results.

Before you go any further, it’s important to know some common terms as they relate to formulas.

A function is a pre-defined formula that returns a value. Functions can be used to simplify a formula. For instance, instead of adding cell A1 with cell A2 and cell A3, you can use the SUM function to add a range of cells.

A reference is any link to a cell name such as A1 or C8. For the most part, your formulas should consist mainly of references. This way, if the data in a referenced cell changes, the formula result will automatically update as well.

An operator is the mathematical symbol that tells Excel which type of calculation is to be performed. There are four different types of calculation operators: (+) means Addition, (-) means Subtraction, (*) means Multiplication and (/) means Division. In addition to calculation operators, you can also use comparison operators such as (=) mean equal, (>) means greater than, (<) means less than, and <> means not equal to.

A constant is a number or text in a formula that does not change. In our example above, only constants and operators were used. Constants should only be used when a number won’t change.

Piecing Together a Formula

• First, all formulas begin with the equal sign (=). Any formula or function not beginning with an equal sign is just text and will not perform any sort of calculation.

•
• After the equal sign is where the cell references, function name, operators and constants appear.

The following table is intended to give you example of how cell references in a function should appear.

 To Designate… Use… An Individual Cell in Column B & Row 11 B11 Multiple Cells Beginning with Cell B11 and Ending with Cell B16 B11:B16 All Cells in Row 10 10:10 All Cells in Column B B:B Multiple Non-Continuous Cells B11,C13,A1

You can see how ranges are separated by a colon (:) and individual cell references are separated by a comma (,).

How Functions Are Different

Instead of individual cell references, you can use a function to shorten or simply your formula. For example, if you wanted to add cells A1, A2, A3, A4 and A5 together, a formula would read:

=A1+A2+A3+A4+A5