The real ball game in a spreadsheet is the use of formulas. This is where you can create those Magical Mystical connections between different cells and get the spreadsheet to do all the work for you.

Subsection(s)

Some quick examples

It's a lot easier to play with formulas than it is to explain them, so we'll do some quick examples.

The first thing you'll notice is that they all start with a special formula character. Most modern spreadsheets use the equals sign (=); some older programs use the plus sign (+).

In either case,

• The first character of every formula must be the formula character
• The formula character has no effect on anything else. More specifically, it doesn't act as an equals sign or a plus sign or anything else.
• The formula character appears exactly once; as the first character in the cell.

Q. 7

What's with the formula character?

For these examples, I've used the equals sign as the formula character. Note, too, that the actual names of the functions may be different in your spreadsheet; use the online help to track down the correct name.

=10 + 5

An incredibly simple formula, it does exactly what you think it would; it adds 10 and 5 and comes up with 15.

=A1

This forms a simple connection between the cell A1 and the cell that contains the formula. It says "Make sure I'm showing whatever cell A1 is showing". As soon as you change the contents of cell A1, this cell will change immediately as well.

=A1 + 10

Pretty much the same as the last one, except this cell will always contain the value of A1 plus 10.

=A1 + B4

Again, the same, but this time we're referring to two cells. Change either one of them and the cell containing the formula will automatically update itself.

=A1/20

A1 divided by 20

=A1 * B1 + C3

Multiplies A1 and B1, and then adds C3

=A1 * (B1 + C3)

Here, we use parenthesis to change the normal order of operations. Add B1 and C3, and then multiply the result by A1.

Remember that arithmetic follows a set order of operations: stuff in parenthesis, followed by multiplication and division, followed by addition and subtraction.

=A1+A2+A3+A4+A5+A6

Add up all the values of cells A1 through A6. Wait, didn't we have a better way to do this?

=sum(A1:A6)

Aha! This uses a built-in function called "sum". It takes one argument (a range of cells to add up). Be careful with the way it looks; formula character, followed by the name of the function, followed by the argument(s) in parenthesis. Again, remember that the actual name may be different for your particular spreadsheet.

=maximum(B1:C4)

Find the biggest value in the range.

=minimum(B1:C4}

Find the smallest value in the range

=maximum(B1:C4) - minimum(B1:C4)

Find the difference between the biggest value and the smallest value in the range B1:C4. Note that the formula character appears once, at the beginning of the formula. Novices often want to put the formula character before every function name.

=average(A1:A10)

As you'd expect, this averages the values in the given range.

Q. 8

How do I find out what built-in functions are available besides sum and average?

Some things to notice about formulas

The formula character

Don't forget that the formula character appears exactly once, that it flags the cell as containing a formula, and that it has no effect on anything else.

Cell value vs. cell appearance

This topic came up before when talking about dates; the text that appears in a cell isn't necessarily the same as the actual underlying value. With formulas, the underlying value is the formula. What appears on the screen depends on the result of the formula and how you have the cell set up to display numbers and text.

Most of the time you will see the RESULTS of the formulas, not the formulas themselves. In the examples later on, we will show you the formulas so you can see what is happening. This diagram might help: Both sides are looking at EXACTLY the same spreadsheet. The only difference is that the results are shown in the left-hand example, and the formulas themselves are shown on the right.

Updates happen instantly

If your formula references other cells, changing the value of a referenced cell immediately results in a change in the cell with the formula. There's no waiting involved; things just go!

Order of operations counts!

Don't forget about order of operations; anything inside parenthesis happens first, followed by multiplication and division, followed by addition and subtraction. When in doubt, use parenthesis to say exactly what you mean. Better safe than sorry.

Empty cells and text cells have a value of zero

Any cell that has nothing in it or has just a string in it has a numeric value of zero. Sometimes your formulas will seem to work but come up with the wrong data -- check to make sure you aren't referencing any empty cells. It can also be useful: if you don't know how long a column is (because you keep adding data to it) you can feel comfortable using a formula like "=sum(A5:A10000)" and know it will come up with a good answer and not freak out.