Chapter 2

Inserting Formulas in a Worksheet

Someone asked me last semester if I could help them memorize the Excel formulas.
You can’t really memorize formulas. You have to know how to write them.

If I asked you to look at the worksheet below and tell me how much it would cost for
potting soil, you would tell me $12. You know that because you see that each bag
costs $3 and you are buying 4 of them, so you would take $3 x 4. When we write
the formula in Excel, we use cell references instead of real numbers. This will let us
change the price or quantity and Excel will automatically recalculate the total.

The formula for the total of the potting soil in Cell D2 would be =B2*C2. Every
formula starts with an = sign. The cell reference for $3.00 is B2, the * sign is how
we multiply in Excel, and the Quantity, 4, is Cell C2.

Item Price Quantity Total

PottingSoil $ 3.00 4
Mulch $ 2.75 3
Petunias $ 0.69 30
So, you see, we don’t really need to memorize formulas. I think about how I would
actually get the number I’m looking for and then convert that into cell references,
mathematical operators, and values.

It is really important in this chapter to read the information and do all of the
exercises within the chapter. Don’t try to do the homework until you have
completed all exercises! I don’t want you getting confused.

Writing Formulas with Mathematical Operators

Become familiar with the commonly used mathematical operators in Table 2.1 on
Page 38. We will be using most of these. Also red about the order of operations
below Table 2.1.

Copying a Formula with Relative Cell References

In the table above, I only need to write the formula for potting soil. I can then copy
that formula down to the mulch and petunias rows. When Excel copies down, it
changes the formula as follows:

=B2*C2 becomes
=B3*C3 for the mulch and
=B4*C4 for the petunias
Great! That is a relative cell reference. Because it knows we are copying it down, it
adds a row number for each formula below it. It works really well. We do this by
using the fill handle that we learned about in Chapter 1. (The little black cross that
you get when you point to the black square in the lower right corner of the cell.)

Project 1a

Try Project 1a. You will be opening ExcelC02Project01.xlsx from the files you copied
in Chapter 1 to your hard drive. These files will be in the Excel2007L1C2 folder. It
should say “Highland Construction”. After you write the formula in 3b and hit enter,
the difference for the Sellar Corporation should be $3,760.00. Copy that formula
down to cells D3 through D10. Click on Cell D3 to see the formula you wrote, now
click on D4 and look how it filled it relatively, now D5 and so on. Cool!! Make the
changes in Step 6 and watch it recalculate!! Leave this workbook open for the next
project.

Copying formulas with the Fill Handle

Try Project 1b on Page 40. Write the formula for Cell D15. The book tells you the
formula is =C15*B15. Do you understand why? How much is Carolyn Bentley
getting paid this week? We are taking her Rate (C15) times her Hours (B15). This
time copy it down with the fill handle to D20. Click on each of those cells and see
how it changed the formulas relatively. Make the changes in Step 4 and watch it
recalculate. Leave this workbook open for the next project.

Writing a Formula by Pointing

I use this all of the time. I’m not very good at figuring out cell names. I always get
them mixed up. So instead of figuring out the old Battleship B5, I click on cell B5
and it puts it in for me!! This is called “pointing” to cells. It is way more accurate for
me. Try it in Project 1c. The number you get for Cell C25 should be 98%. Copy it
down relatively to D31. See, formulas aren’t so hard!! Leave it open still….

Using the Trace Error Button

When Excel thinks you have made a mistake, it will give you a smart tag with an
exclamation point in a yellow diamond. This is the Trace Error button. Read about
it on Page 41 and then do Project 1d. Do you understand the formula in Step 1h?
We take the Total Hours that the machine is available minus the amount of time the
machine is in use. Then we take that whole amount divided by the Total Hours
Available to get the % of down time. Excel calculates the part in the parenthesis
first. Make sure you don’t just type in the formula! Point to the cells and click on
them to make the cell references appear.

The green triangle and Trace Error button shows up because it thinks we have a
mistake. The formula omits adjacent cells. This means that it thinks we should be
adding those numbers all the way across, not just January through March. Follow
the instructions in Step 5 and 6 to ignore the errors.

Inserting Formulas with Functions

Functions are built-in formulas. Excel helps you write more complex formulas by
giving you what we used to call the “Function Wizard”. It just walks you through
creating formulas. One of the functions that we have already used is the Sum
function. We put it in with the Sum button. Read Page 43 to learn about functions.
There are over 200 functions. I only use about 12 of them! It is really interesting to
see some of the things that Excel will do for you.

Writing Formulas with Statistical Functions

In Project 2a you will find the averages of test scores. In Project 2b, you find the
maximum and minimum test scores. In Project 2b, make sure you try out the new
Formula AutoComplete feature in Step 2. This is so cool!! Instead of typing in
E14:E16, I drag over the top of them and let Excel put it in. They will show you three
different ways to enter the functions. Leave this open for the next project.

Displaying Formulas

Try Project 2d to see how to turn on and off the formulas. It is a toggle—do it once
to turn it on and again to turn it off.

Writing Formulas with Financial Functions

In project 3a, make sure you read carefully. In step 3d, when you click in the Rate
box, read the Rate description in the center section. This always helps me to
remember what I should put in the box. It says: Rate is the interest rate per period
for the loan. For example, use 6%/4 for quarterly payments at 6% APR. This reminds
me that if the payments are quarterly, divide it by 4; if the payments are monthly,
divide it by 12. You will put C5/12—always use cell references, not real numbers. In
the PV box, this text always has you put in a minus sign before the cell reference. If
you don’t, the number will be a negative number (because you are paying out
money). Follow the instructions to put a minus sign first and then B5. The monthly
payment will be $316.98. Finish the project and leave it open.

In Project 3b, you will be finding the Future Value of an Investment. Leave it open

Writing Formulas with Date and Time Functions

Ready the description on the bottom of 51. How many days have there been since
January 1, 1900? Try Project 3c. When you are in the Function Arguments dialog box
and have put in the year, month, and day, look directly below the three text boxes
to see the serial number—the number of days between then and January 1, 1900.
For March 24, 2010 it is 40261. Excel needs the date as a serial number to be able
to calculate a due date that is 30 days away. After finding the due date, you will put
in the NOW function. Close this.

Writing a Formula with the IF Logical Function

Open the file for Project 4a. Can you tell if

Allejandro should get a bonus? If he met his
quota (if C5>B5), he gets 15% of the actual
sales. That is your logical test. The next part of
the formula is the true part. If he has met his
bonus you will give him 15 of Sales. (C5*15%).
The next part is the false part. If he hasn’t, he
gets 0. I use the IF function dialog box to write my if statements. Click the Formula
tab, then logical, then IF. It will walk you through it. This is what mine looked like.

Look at this web site if you need a little more info on how the If statement works:

http://office.tizag.com/excelTutorial/ifstatement.php

Try Nested IFs on Pages 54 and 55. I would like you to see how they are written. I
won’t test you over nested IFs. Try Projects 4b and 4c to try nested IFs.

Using Absolute and Mixed Cell References

We use and absolute reference if the cell absolutely has to stay on that cell and
not move down or over. If you want to see how this works, do it wrong! In Step 3a
instead of $B$12, make it B12 and copy it down. You will see the errors. Click on
each cell and see what the formula is. It copies it down relatively. We need cell B12
to absolutely stay there and not move. Cell B12 is 1.2. This means we are not taking
it times 20% but times 120% This makes it the planned amount plus 20% more.
Leave this open.

In Project 5b, which of the cell references cannot move? Those are the ones that
you have to push F4 to make them absolute. Do Project 5b. Mine didn’t come out
with the same Weighted Average numbers as the picture in the book. This is
because the book has the Training Weights of 30%, 30%, and 50%. The last one
should actually be 40%. My Weighted Average numbers were 80%, 67%, 91%, 83%,
89%, and 60%.

In Project 5c, Excel does the part in the parenthesis first. (B29*$B$36*D29). This
figures the person’s overtime amount (Their hourly wage * the overtime rate of 1.5
which has to be absolute and not move from there * how many overtime hours they
worked). Then it adds that to B29*C29 their regular pay. (Hourly Wage * Hours).

In Project 5d, you are using mixed cell references. Think about which cell you want
to move. Do you want the column to move or the row to move? Read the bottom of
Page 58 for an explanation.