Module 2 Homework Assignment
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 1
BASIC FUNCTIONS AND CELL ADDRESSING
The following course notes for Module 2 will concentrate on copying formulas, and on relative,
mixed and absolute cell addressing. We will also cover some basic Excel functions, named ranges,
and common errors in Excel.
COPYING FORMULAS
One of the major advantages of using an electronic spreadsheet application is the ability to write
formulas that can reference other cells. Changes to a value in a cell will automatically affect all
other cells with formulas that refer to this value directly or indirectly. Often, it is not only
necessary to reuse specific values over and over again, but also to apply the same formula many
times to different inputs.
For example, consider a spreadsheet where each column contains a series of line item costs such
as rent, food, and electricity in rows 5 through 7, and each column represents a different month,
in column B January, in column C February, etc. To total rows 5 through 7 in column B, use the
formula =B5+B6+B7. To create similar totals in columns C and D use the formulas
=C5+C6+C7 and =D5+D6+D7, respectively. The only difference between the three formulas
is the column being referenced. Writing these formulas out individually is a tedious, repetitive
and time consuming task, especially if there are hundreds of cost categories and dozens of months.
Even worse, is the real possibility of at least one typo or mistake! Wouldn’t it be nice if the
computer could be told “use the same formula but just change the cell references?”
When text is copied in a word processor application, a copy of the selected text is made and then
this exact text is pasted in the location indicated. Copying text and values in Excel works exactly
the same way. When copying a value such as the number 5, it will be pasted as the number 5.
However, when copying a formula containing cell references, Excel will change these references
relative to where the formula is being pasted. If the formula =B5+B6+B7 is copied from one
column and pasted in the next column to the right, it would become =C5+C6+C7. This allows
for use of a “general” formula over and over again, but with respect of a different set of numbers.
To understand how cell references change when formulas are copied, we will need to understand
cell referencing and how to apply Absolute, Mixed and Relative cell addressing.
COPYING CELL REFERENCES IN A FORMULA
So, there are three type of cell referencing: relative, mixed and absolute. Let’s study each type and
provide some examples for each.
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 2
RELATIVE CELL REFERENCING
Consider the spreadsheet in Figure 1 which summarizes
a salesman’s trip expenses. In column B are the costs for
travel for the corresponding month; January, February
and March. In columns C and D are the corresponding
monthly costs for lodging and food. To summarize travel
costs over this 3-month period, in cell B7 write the
formula =B4+B5+B6. A similar formula will be needed
in cell C7 and D7 to total the corresponding costs for
lodging and food. Instead of rewriting the formula, it
can be copied. If the formula in cell B7 is copied into cell C7, one column to the right of the
original formula, the operands B4, B5, B6 within the formula will change by the same relative
distance: one column to the right. This will result in the formula =C4+C5+C6.
Cell references such as B4, B5 and B6 are referred to as relative cell references. Relative cell
references change when they are copied relative to their displacement. Cell references can also
be designated as absolute cell references. Absolute cell references do not change, even when
copied into different cells. When copying a formula, if a cell reference is relative, the computer
will automatically change that reference relative to where the formula is being moved. Cell
references are relative by default; no special characters are required to designate a reference as
relative.
In the example above in Figure 1, when the formula in cell B7 =B4+B5+B6 is copied into cell C7,
Excel will automatically adjust all relative cell references in the formula the same number of
columns/rows that the formula was moved. The displacement from B7 to C7 is one column
and zero rows. Therefore one column and zero rows will be added to each relative cell reference
in the formula. B4 plus 1 column and 0 rows is C4, B5 plus 1 column and 0 rows is C5,
etc. The resulting formula in cell C7 will be =C4+C5+C6.
NOTE: to copy one cell to an adjacent cell, you can use a fill handle. A fill handle is a feature to
extend (and fill) a series of numbers, dates, or even text to a desired number of cells. In the active
cell of the spreadsheet, the fill handle is a small black box at the bottom right corner, as shown in
the Figure 2 below.
1
2
3
4
5
6
7
A B C D
Month Travel Lodging Food
Jan 575 125 420
Feb 215 212 185
Mar 810 0 45
Total 1600 337 650
Monthly Travel Costs
Figure 1
Figure 2
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 3
For example, if you enter the number "1" in cell A1 and the number "2" in cell A2 as is shown in
the rightmost image above, you could extend that numbering sequence down through as many
cells as you want. You can do this by selecting both cells, then clicking the fill handle (the small
black box in the lower righthand corner) with the left mouse button. While holding the left mouse
down, drag the mouse down the spreadsheet in column A. If you dragged down to cell A50 and
release the mouse button, cells A1 through A50 would now be sequentially numbered 1 to 50
without having to type each number individually.
Another example of the fill handle, is to enter "5" into cell A1, then "10" into cell A3. After that,
drag a box around cells A1 though A4 and then drag the fill handle down as far as you want. This
series of actions makes column A have 5, 10, 15, 20, 25, etc., with spaces in-between each cell.
Examples of how formulas are displaced when copied:
1. What formula will result if the formula =B2+C2/D4 is copied from cell E8 to cell E12?
• The displacement from E8 to cell E12 is 0 columns and 4 rows. This would be applied to each relative cell reference in the formula.
• In the first operand B2, column B plus 0 columns would remain column B. Row 2 plus 4 rows would become row 6. So the resulting cell reference would be B6.
• Similarly, the second operand C2 would become C6 and the third operand D4 would become D8.
• In cell F12 the resulting formula would be =B6+C6/D8.
2. What formula will result if the formula =B6–C7+2 is copied from cell D9 into cell F12?
• The displacement from D9 to F12 is 2 columns and 3 rows. This would be applied to each relative cell reference in the formula.
• In the first operand B6, column B plus 2 columns would become column D. Row 6 plus 3 rows would become row 9. The resulting cell reference would be D9.
• The second operand C7 would become E10.
• As the value 2 is a constant, it does not change.
• The resulting formula in cell F12 would be =D9–E10+2.
To physically copy a formula in Excel, one can use the copy/paste features from Clipboard group
on the Home ribbon. A more common method of copying a formula into an adjacent cell or cells
is to use the Fill Handle. When the mouse pointer is moved to the lower right hand corner of
the active cell it turns the pointer into a plus sign, which is called the fill handle. Once the fill
handle appears, hold down the left mouse button and drag it into the desired cell or cells where
the formula is to be copied and release. The formula will be automatically pasted into each cell in
the selected area.
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 4
ABSOLUTE CELL REFERENCING
Consider the travel cost spreadsheet in Figure 3. This spreadsheet keeps track of a salesman’s
monthly expenditures for travel and lodging, as listed columns B and C respectively. All
arrangements for this travel are made by a local travel agent who charges a service fee of 5% of
the base price. The worksheet will need to be completed by calculating each of these expenditures
including this fee. Can this be done by writing one formula in cell D6 and copying it down two
rows and then across one column?
January’s travel cost including the service fee can be calculated by multiplying the base price of
$575 (B6) by one plus the service fee of 5% (B1). The resulting value will include the base price
plus the additional 5%. Translated into Excel syntax the formula in cell D6 will be =B6*(1+B1).
Similar formulas will be required in cells D7 and D8 to calculate the total cost of travel in February
and March including the service fee, and in cells E6, E7 and E8 to calculate the costs of lodging.
Consider first copying the formula =B6*(1+B1) from cell D6 to cell D7, a displacement of 0
columns and 1 row. The resulting formula would be =B7*(1+B2). Does this give the correct
result? No, the new formula multiplies February’s travel costs by the value in cell B2, a blank cell.
What is really needed here is to change the travel cost reference relatively (February instead of
January), but keep the service fee cell reference the same. Likewise, the formula required in cell
E6 to calculate the cost of lodging for January will also require a relative cell reference for the base
cost and the value of the service fee to remain unchanged.
This can be accomplished by using absolute cell referencing for the cell reference containing
the service fee percentage (B1). An absolute cell reference is one that does not change
regardless of where it is copied. To indicate that a cell reference is absolute, dollar signs ($)
are placed in front of both the column letter and row number of the cell address. The correct
formula for cell D6 in the worksheet above would then be =B6*(1+$B$1). When this formula is
copied from cell D6 to D7, a displacement of 0 columns and 1 row, it will be changed to
=B7*(1+$B$1). The travel cost reference (B7) reflects the displacement while the cost
percentage, as it is referenced absolutely, does not change. Likewise, when the formula is copied
from D6 to E6 (1 column and 0 rows) it will be changed to =C6*(1+$B$1).
Figure 3
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 5
MIXED CELL REFERENCING
In Figure 4 is another travel cost worksheet. This worksheet summarizes the travel
reimbursement for mileage to and from two different field sites over a period of three months.
The monthly reimbursement costs are based on the number of trips taken (B5:C7) and the cost
per trip. Travel to site A is reimbursed at $125 per trip (D2) and travel to site B at $175 per trip
(E2).
The formula =B5*D2 can be written in cell D5 to
calculate the total reimbursement for January trips
to site A. Similar formulas will be needed in cells
D6:D7 to calculate reimbursements for February
and March to Site A and in cells E5:E7 to calcuate
reimbursement costs for trips to site B. Can this
formula be copied down the column and across the
row as written?
Consider the following:
• When copying down, what formula will be needed in cell D6 to calculate the reimbursement costs for site A trips in February? The number of trips is 2 (cell B6) and the price is $125 (D2). The formula should be =B6*D2. If the original formula written in cell D5, =B5*D2, is copied to cell D6, a displacement of 0 columns and 1 row, the resulting formula would be =B6*D3. The reference to the cell containing the number of trips (B6) is correct, but the reference to cell containing the cost per trip for site A (D2) is not. The row in the cell reference D2 should not change.
• When copying across, what formula will be needed in cell E5 to calculate the reimbursement costs for trips to site B in January? The number of trips is 2 (cell C5) and the price is $175 (E2). The correct formula is =C5*E2. If the original formula written in cell D5, =B5*D2, is copied to cell E5, a displacement of 1 column and 0 rows, the resulting formula would be =C5*E2. Both the reference to cell containing the number of trips (C6) and the reference to cell containing the cost per trip for Site B (E2) are correct.
Thus the cell reference for the number of trips changes relatively regardless of whether it is copied
down or across, but the cell reference for the cost per trip should only change when copied across
but not when copied down. How can Excel be instructed to change only part of a cell reference,
the column-but not the row when it is copied? A technique known as mixed (or partial) relative
and absolute cell referencing allows part of a cell reference to be absolute by placing an absolute
sign ($) in front of only that part of the reference. The formula =B5*D$2 can be written in cell
D5 and successfully copied both down the column and across the row to calculate the
corresponding reimbursement for site. Since there is a $ in front of the row in the reference D$2,
the row will not change when the formula is copied.
Figure 4
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 6
However, since there is no $ in front of the column in D$2, the column will change when copied
across. This same technique can be used in a cell reference to hold the column absolute but allow
the row to change.
Consider the spreadsheet in Figure 5. The
orientation of this worksheet is somewhat
different than the one in Figure 4 above. Trip
prices are now listed vertically in cells B3:B5.
Instead of costs per month, this worksheet
calculates trip costs by department; for the Sales
Group and Service Group respectively. To
calculate the total costs for the Sales Group’s trips
to site A multiply the trip cost to site A (B3) by the
number of trips (C3); the corresponding formula is =B3*C3. Again this will need to be copied
down the column and across the row to calculate the costs for sites B and C and the Service group.
What modifications will be needed to the formula so that the copied formulas will result in correct
values for these other sites and the the Service Group?
The value in cell B3, $/trip, should vary when the formula is copied down the column to correctly
give the $/trip for sites B and C but should not vary when copied across the row to
calculate Site A costs for the Service Group. The value in cell C3, # of trips taken by the Sales
group to Site A, should vary both when copied down and across to correctly give the number of
trips for the corresponding site and group. Then the only element of the formula that remains the
same when copied is the column reference in the operand B3. The final formula that can be placed
in cell E3 and copied both down and across is =$B3*C3.
Example of how formulas are changed when copied:
If the formula =B5+B$5+$B5+$B$5 is copied from cell D1 into cell E3, what formula will result?
B5, B$5, $B5, $B$5 all reference the same cell. The difference between these references is in how they will be copied.
• First determine the number of rows and columns the formula is being moved. In this case,
the displacement from column D to E is one column and the displacement from row 1 to 3
is two rows.
• Apply the displacement of 1 column and 2 rows to each part of each cell reference.
• B5 is a relative reference, so column B plus 1 column will become column C and row 5 plus 2 rows will become row 7. The new reference is C7.
• B$5 is relative with respect to the column and absolute with respect to the row. So column B will become C and row $5 will remain the same. The new reference is C$5.
• $B5 is absolute with respect to the column and relative with respect to the row. So $B will remain the same and 5 will become 7. The new reference will be $B7.
Figure 5
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 7
• $B$5 is absolute with respect to both column and row; there will be no change when the formula is displaced.
• Combining these references results in the formula =C7+C$5+$B7+$B$5.
COPYING FORMULAS UP & TO THE LEFT
What formula would result if the formula =B2+1 is copied from cell C2 into cell B1? Thus far the
examples shown have only displaced formulas to the right and down within the worksheet. Can
formulas be copied to rows above and columns to the left of the original formula? Yes. In this
example the formula in cell C2 is being displaced minus 1 column and minus 1 row, resulting in
the new formula =A1+1.
What formula results if =B2+1 is copied from cell C2 into cell A1? The displacement form cell C2
to cell A1 is minus 2 columns and minus 1 row. How can the cell reference B2 be displaced minus
2 columns? There is no such column! In this case, Excel will create the formula =#REF!+1 in
cell A1. The value displayed in the cell will be the error message #REF! indicating an illegal cell
reference. Displacing a formula such that the new formula has an invalid row number also results
in a #REF! error. If multiple cell references are invalid then a #REF! error will be inserted at
each part of the formula where this occurs.
NAMED RANGES
Another tool that is provided in Excel is the ability to name a cell or range of cells and later use
these names within formulas. When a cell or cells (i.e., range) are identified with a name, this is
referred to as a Named Range. There are two methods to naming a cell or range of cells:
• Highlight the cell or cell(s) and type a name in the Name Box which appears in the upper left hand part of the window just to the left of the Formula Bar.
• Use the Name Manger tool in the Defined Names Group on the Formulas Ribbon tab.
Named ranges work differently than using cell references when copying formulas. Named ranges
are always treated as absolute cell references. They are useful in cases where a cell is
required in many formulas and is always addressed absolutely. In such a case, it may be easier to
give the cell a name and use that name in your formulas. Recall the spreadsheet used previously,
as seen in Figure 6 below.
Figure 6
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 8
If cell B1 is given the name fee, the formula that was written in cell D6, =B6*$B$1, could be
rewritten as =B6*fee. This will make the formula easier to read and understand. Could the cell
reference B6 be given a name such as JanTravel and used in the formula? This would present
a problem when it is copied as B6 must be copied relatively; a named range would not. Named
ranges are best used with constants that don’t change or ranges that will be reused over and over
again (we will see such examples in subsequent chapters).
ERROR MESSAGES
For those of you who never type a wrong keystroke, this section may not be of great importance.
However, for the rest of us, knowing how to read and understand errors messages can come in
very handy. The error message #REF! was previously introduced in the discussion on copying
formulas. It is important to also be aware of several other common error messages so that you
can quickly and easily debug your spreadsheets. Listed below are the most common error message
symbols. These messages are specific to the Excel application; different spreadsheet applications
may use different error message symbols.
##### Numeric value too wide to display
#DIV/0! Divide by 0 occurs
#N/A Data being referenced is not available
#NAME? Named Range in formula is not recognized
#NUM! Problem with a number in a formula or function
#REF! Cell reference is not valid
#VALUE! Wrong type of argument or operand in a formula
In subsequent modules you will see examples of when these error messages are likely to be displayed and how to correct the spreadsheet accordingly.
USING ARITHMETIC FUN CTIONS
In addition to writing formulas that use constants, cell references, and operands, formulas may
also include functions. Functions are predefined formulas that perform specific calculations.
When a function is used in a formula, the user only needs to supply the function’s inputs
(arguments) and the result of the function’s calculation will be used when the computer evaluates
the formula. This section will present several different commonly used functions: SUM,
AVERAGE, COUNT, COUNTA, MIN and MAX. Functions that can be used to perform more
sophisticated analyses, such as ROUND will also be introduced.
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 9
THE SUM FUNCTION
The simplest and most commonly used function is the SUM function, which is designed to add a
list of values. These values may be input directly into the formula as constants, references to cells,
or ranges of cells containing arithmetic values. The syntax of the SUM function is as follows:
=SUM(number1, [number2] …)
All Excel functions share a common syntactical structure: a function name followed by an open
left parenthesis, then a list of arguments - inputs needed by the function in a specific order
separated by commas, and finally a closing right parenthesis. Some function arguments are
required and some are optional. The required arguments will be written in bold text and the
optional arguments will be enclosed in brackets [ ]. Each function has a unique function name
and specific list of arguments. In addition to a function’s syntax, each function has a set of rules
that must be followed so that the function returns the expected result.
An example of a SUM function that adds the values in the cells A5 to A100 is =SUM(A5:A100).
Functions can be used by themselves or in combination with other operands as part of a larger
formula for example =SUM(A2:A7)/B8-SUM(5,7,B35, A8:C8). Notice that in this example
the formula contains two SUM functions as well as the division and subtraction operators. The
first SUM function will add all of the values in cells A2, A3, A4, A5, A6 and A7. The second SUM
function will add the constant values 7 and 5, the value in cell B35 and the values in cells A8, B8
and C8. If these cells to not contain a numeric values (are blank, contain text or Boolean values)
the function will ignore them.
The SUM function is useful for both saving time and making a spreadsheet robust to changes. The
formula =A1+A2+A3 accomplishes the same task as the formula =SUM(A1:A3), so why bother
to use the SUM function? First, the SUM function can save time. Consider how tedious it would
be to sum the cells A1 through A100 using only the additional operator (+). It is much more
efficient to write =SUM(A1:A100) than it is to type out the corresponding addition. Also
consider if a row were to be inserted in between A1 and A2. If the inserted value needs to be
included in the summation, the formula would need to be modified as follows: =A1+A2+A3+A4.
When inserting rows and/or columns into a worksheet, Excel will automatically update cell
references within formulas. If the formula =SUM(A1:A3) is used, since the old A3 becomes A4,
the range would update to =SUM(A1:A4), automatically including the inserted row. Using
functions when appropriate is considered best practice and will be expected in this course.
INSERTING FUNCTIONS INTO A FORMULA
There are several different methods available for entering functions in a formula. When writing
a formula, at the point where a function is to be inserted, use one of the following techniques:
• Manually type in the function name, open parenthesis, and each of the arguments as required. Once the function name and parenthesis has been typed, the computer will display the syntax of the function directly below the active cell. When entering cell references as
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 10
arguments either directly type the cell reference directly into the formula or use the mouse to click on the desired cell or range of cells. Clicking on the cells while typing a formula will automatically incorporate the reference as part of the formula. This method is quick but requires the user to know the exact function name. Remember, if you only enter a function, then it must start with = for Excel to recognize it as such.
• Use the AutoSum ∑ button for the SUM, AVERAGE, MIN, MAX or COUNT functions only, located both on the Home and Formulas tab. When using this feature, Excel will typically “sense” what range of numbers is to be added, averaged, etc. This range can be modified by the user.
• Use the Insert Function Tool to access all available functions in Excel by clicking on the
button next to the Formula Bar or on the Formulas Ribbon. As seen in Figure 7, a dialog box will appear listing function names and categories. To find a specific function, use the search feature or select from one of the categories and click on the name of the desired function. This will open another dialog box (Figure 8) containing the function name and input areas for each of the function’s arguments.
FUNCTIONS WITH ONE ARGUMENT TYPE
The functions AVERAGE, MIN, MAX, COUNT, and COUNTA are similar in syntax to the SUM
function in that their arguments are just a list of numbers. The syntax of these functions are as
follows:
Function-name(arguments) Function Descriptions
SUM(number1, [number2] …) Calculates the sum of a list of values
AVERAGE(number1, [number2] ...) Calculates the average value of a list
MIN(number1, [number2] …) Returns the minimum value in a list
MAX(number1, [number2] …) Returns the maximum value in a list
COUNT(number1, [number2] …) Determines the number of numeric values in a list
Figure 8 Figure 7
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 11
COUNTA(number1, [number2] …) Determines the number of numeric and non-numeric values in a list
The SUM, MIN, MAX, AVERAGE COUNT, and COUNTA functions are simple in that they only
contain one type of argument – a list of values. Unlike other functions that will be presented later,
the order of the list does not matter. The formula =MIN(B2,B5,7) will return an identical result
as the formula =MIN(7,B2,B5). Each argument can be a constant, a single cell reference or even
a range of cells. Examples of ranges of cells are:
• B2:B7 - a range down a column that includes cells B2, B3, B4, B5, B6, B7
• B2:E2 - a range along a row that includes cells B2,C2,D2,E2
• B2:E7 - a 2-diminsional range including all cells within – B2, B3, B4, …B7, C2, C3, C4...
C7… all the way to E7
To see how different formulas can be combined, consider the formula
=MAX(A1:A100)/SUM(B2:X2,50,A99). The first operand will be the result of the MAX
function. In this case, the MAX function will find the highest value in the column range starting
in row 1 and going through row 100 of column A. The result of this MAX function will then be
divided by the result of the SUM function from the denominator. The SUM function contains three
elements – a range along row 2 from columns B through X, the constant value 50, and the value
contained in cell A99.
Detailed descriptions of each function including their syntax, rules of use, and examples can be found using the Help feature of Excel or online at Microsoft support.
A FUNCTION’S RULES & SPECIFICATIONS
Consider the worksheet in Figure 9 below. This worksheet lists the service ratings for several
different phone providers by region (East, Midwest, South and West). Each provider is given a
rating on a 100 point scale, where 100 is a perfect score. In cell C10 the formula
=AVERAGE(C5:C8) calculates the average of the values in cell C5,C6,C7 and C8. This formula
is copied across the row into cells D10:F10.
Figure 9
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 12
Notice cell D8 is blank. Should the average be calculated as 90+80+70+0 divided by 4 or
90+80+70 divided by 3? There is no one right answer to the question as to whether or not the
Midwest Service score for Spruce be counted as a zero or ignored, it will depend on how the values
are being used. The designers of Excel decided between these two possibilities in the rules of the
AVERAGE function.
The rules of the AVERAGE Excel function state that blank cells are ignored. When Excel
evaluates the formula =AVERAGE(D5:D8) it adds 90+80+70 and divides the result by 3. A
zero would need to be placed in cell D8 to count the blank cell as part of the average.
The rules for SUM, COUNT, COUNTA, MIN, and MAX are similar. If a range includes cells that
do not contain arithmetic values, but contain labels or are blank, the function will ignore them.
An attempt to count the number of phone providers in the survey might use the formula
=COUNT(A5:A8). However, this formula will result in the value 0 as cells A5 through A8
contain text, not numerical values. Instead, use the formula =COUNTA(A5:A8), which will
return the value 4.
Why do we need to observe these rules so closely? A computer will take the inputs of a function
and apply a list of well-defined instructions to obtain the results. This defined step-by-step
method determining a function’s results is known as an algorithm. All of the functions in Excel
have an underlying algorithm which the programmer has coded into the software. This ensures
that every time a specific function is used a uniform methodology is applied.
A FEW MORE TIPS TO USING FUNCTIONS
As mentioned previously, functions can be used by themselves or in combination with other
operands as part of a larger formula for example: =SUM(A2:A7)/Count(A1:E1)*5. It is also
possible to nest functions inside of each other. For example, the formula
=MAX(Sum(1,5,10),25) will first calculate a value for the SUM function (16) and then use the
result as an argument of the MAX function (comparing 16 to 25). The final value will be 25. Excel
will allow up to 64 levels of nesting.
What Not to do:
A frequent mistake made when inputting data into a spreadsheet is
to input a number as a text label. Figure 10 illustrates how Excel
shows the difference between numbers input as text (the characters
2 and 5) and numbers input as numeric values (the number 25). A
number can be input as text if a quotation mark (single or double) is
entered first (e.g. ’25) or if the value is copied from another software
application as text and pasted into Excel.
Excel does not consistently recognize labels as numerical values, so this can create problems. For
example, the formula =B1+B2 will result in the value 50 but the formula =SUM(B1:B2) will
1
2
3
A B
number 25
text 25
sum 25
Figure 10
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 13
A SIMPLE PROBLEM
In order to solve a problem, one must translate the problem into mathematical formulas or
functions and then encode these formulas or functions into the proper syntax.
One method of translating a problem and encoding it to a spreadsheet is to use the following 3-
step process:
• Step 1: Determine what the problem is asking and how you would solve it without using a computer. This may be an algebraic expression, counting, averaging, making a logical decision, finding something from a list, etc.
• Step2: Translate your solution into Excel syntax using the necessary values, cell references, operands, and functions.
• Step 3: Determine if this formula is to be copied and include absolute or mixed cell referencing only as needed.
For very simple problems these steps can be gone through quickly, in more complex problems
writing out each successive step will aid in developing a correct solution.
In Figure 11 is a similar worksheet to the one seen previously. Now answer the following questions
using this approach to write the appropriate Excel formulas.
result in the value 25. Unless modified, Excel formats numerical values as right justified and text
labels as left justified making it easy to spot this problem. Often Excel will also place an error
alert, a small green triangle, in the upper left hand corner of a cell containing a number input as
text.
If you have created a spreadsheet with a column of numbers that do not add up properly,
frequently the mistake is that one or more of the cells contain a label, instead of a value. Be very
careful of data copied from other sources, especially since the resulting spreadsheet may right
justify text making it difficult to detect this error.
Figure 11
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 14
Question #1: Write a formula in cell G5 to total the service rating points for BT&T. Assume this
formula will be copied down the column to total the ratings for each of the other service providers.
• Step 1 – What does the problem ask to be calculated? The total of the ratings for BT&T is the sum of the values in cells C5 through F5. Algebraically this can be expressed as =81+90+88+86.
• Step 2 – Translate into Excel syntax. In Excel syntax this can be either =C5+D5+E5+F5 or =SUM(C5:F5). In this case, either formula is correct. However, the SUM function is quicker and easier to use.
• Step 3 – Is the formula =SUM(C5:F5) being copied? Yes, this formula will be copied down the column to find the corresponding value for each of the other phone providers. When G5 is copied to G6 and beyond, does the cell reference C5 in the formula change relative to the new position? Does F5 change relative to the new position? Yes, both of these terms will change relatively, the new formula in cell G6 will be =SUM(C6:F6). No absolute or mixed cell referencing is required.
What Not to do:
Often inexperienced spreadsheet users will write the solution as follows:
• =SUM(C5,D5,E5,F5) while this is valid it is not recommended as a continuous range is
more simply represented by typing C5:F5. Non-continuous cells and ranges must be listed
out explicitly (=SUM(C5,Z125,A3)).
• =SUM(C5+D5+E5+F5) again while this is valid it is not recommended as it nests the
addition within the function The computer will first add these values reducing the formula
to =SUM(345) and then to just 345. The SUM function is superfluous since the values are
already added.
Question #2: Write an Excel formula in cell H5, which can be copied down the column, to
calculate the total points received by each phone provider as a percentage of the total possible
points.
• The total points received by BT&T are 345 and the total possible points are 400. The percentage would then be 345/400.
• The total of the points received by BT&T is listed in cell G5 and the total possible points are listed in cell G3. The arithmetic expression 345/400 can then be translated into the formula =G5/G3. No function is required, just a division operation.
• This formula is being copied down the column. If it is copied relatively, the resulting formula in cell H6 will be =G6/G4. The numerator G6 is correct; G6 is the corresponding total points received by Blue Phone. However, the denominator is incorrect. The total possible points should remain 400, so the cell G3 should remain the same. Therefore, the row of the denominator cell reference should be held absolute. The final formula will be =G5/G$3.
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 15
What Not to do:
Why not write the formula like this =$G5/$G$3 – adding in absolute cell references in front of
the column addresses? When copying a formula down a column only, the column letter never
changes. Thus placing a $ in front of each column address would be unnecessary. To keep
things clear and simple only add an absolute ($) cell reference if absolutely
necessary.
Compare =$G5/$G$3 vs. =G5/G$3. Even for this simple formula the latter expression is easier
to read. If this formula is only being copied down the column, it’s very hard to see what rows
are relative and absolute in the first formula, whereas it’s extremely clear what is relative and
absolute in the second formula.
Question #3: Write an Excel formula in cell C11, which can be copied across the row, to
determine the poorest service rating in each region.
Go through the 3-step process. The answer you should obtain is: =MIN(C5:C8).
When copied into cell D11 will this formula result in the value 70 or the value 0? Since the MIN
function ignores blank cells (D8) the minimum rating would be 70.
Question #4: Write a formula in cell C12, which can be copied across the row, to calculate the
number of phone providers surveyed in each region.
• Determining the number of providers in the East region involves counting the number of non- blank cells listed in column C of the worksheet.
• Counting the number of non-blank cells can be accomplished using the COUNT function. In Excel syntax this corresponds to the formula =COUNT(C5:C8).
• When copied across the range, the reference C5:C8 will change relatively, so no absolute or mixed cell referencing is needed.
Question #5: Write a formula in cell C10, which can be copied across the row, to calculate the
average rating in each region.
Will the formula =AVERAGE(C5:C8) always give you the same result as =SUM(C5:C8)/4?
• Remember, the algorithm for the AVERAGE function ignores cells that are blank or contain labels. So if any cell in the range C5:C8 contained a non-numeric value, while the sum of the values would not change the denominator 4 would. The AVERAGE function will divide the sum by the number of cells containing numeric values. Thus the formulas will not result in the same value. An equivalent formula to =AVERAGE(C5:C8) would be =SUM(C5:C8)/COUNT(C5:C8).
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 16
THE ROUND FUNCTION
Again consider the ratings spreadsheet as seen in Figure 12 below. If the values in cells C5:C8 are
averaged, the precise value of 68.75 will result. Shown in cell C10 is the result of the formula
=AVERAGE(C5:C8) displayed with zero decimal places, 69. What if you are instructed to
estimate the number of households which found this provider acceptable by multiplying the
average rating rounded to the nearest whole number by 1,000? If the formula =C10*1000 is used
the resulting value will be 68,750 not 69000. How can the precise value in cell C10 be rounded,
not just the display?
THE ROUND FUNCTION SYNTAX
The ROUND function can be used to round a specific value to a specified number of decimal
places. The syntax of the ROUND function is:
=ROUND(number, num_digits)
The Round function is different than the functions seen thus far in that it has more than one type
of argument, each with a different meaning. In addition the order of its arguments will affect the
value calculated by the function. The ROUND function’s arguments are as follows:
• Number: The first argument is a single value that can be a constant, a cell reference where the cell contains a numerical value, or a nested formula that results in a single number value.
• Num_digits: The second argument is the specified number of decimal places. For example, a value of 0 for the second argument tells the computer to round to the nearest whole number. A value of 1 for the second argument tells the computer to round to the nearest tenth (0.1, 0.2 ..). A value of -2 for the second argument tells the computer to round to the nearest hundred (100,200…).
The ROUND function algorithm rounds down all values of less than half the range, and rounds
up values from half the range and above. For example = ROUND(1.49,0) will result in the value
Figure 12
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 17
1 while the formula = ROUND(1.50,0) will result in the value 2. As with all other functions, the
ROUND function can be used alone, as part of larger formulas, or nested inside of other functions.
So to answer the original question of how to take the average rating score rounded to the nearest
whole number and then multiply by 1000, write the formula =ROUND(C10,0). Unlike changing
the display of a cell, this function will actually convert that number’s precision. There are many
other similar functions in Excel such as INT and ROUNDUP that also alter the precision of a
value. We will not be using these in this class. Use the Function Wizard to explore some of these
other functions.
SOME SIMPLE EXAMPLES OF USING THE ROUND FUNCTION
Consider a spreadsheet with cell A1 containing the numeric value 78.43, and cell A2 containing
the numeric value 78.686788. Here are some additional examples of formulas using the ROUND
function:
• The formula =ROUND(A2,2) results in the value 78.69. The first argument tells the
computer to take the value in cell A2, which is 78.686788. The second argument indicates
that this value is to be rounded to the nearest hundredth.
• The formula =ROUND(A2,0) results in the value 79
• The formula =ROUND(A2,-1)+5 results in the value 85. First the ROUND function will be
evaluated, resulting in the value 80. Then the number 5 will be added, resulting in 85. Notice
that rounding to the -1 decimal place is the same as rounding to the nearest ten.
• The formula =ROUND(0.1+Min(A1, A2),0) results in the value 79. The computer will first
evaluate the MIN function, resulting in 78.43. Adding 0.1 to 78.43 is 78.53. Rounding this to
zero decimal places results in 79.
ROUNDING MULTIPLE VALUES
How can 69.98 rounded to the nearest tenth be added to 3.01 rounded to the nearest tenth?
Does the formula =ROUND(69.98,3.01,1) correctly implement this calculation? No, Excel will
alert the user that there are too many arguments in this function as it would interpret 69.98 as
the value to be rounded, 3.01 as the number of decimal places to round to, and not know what to
do with the value 1.
Does the formula =ROUND(69.98+3.01,1) correctly implement this calculation? No. This
formula rounds the values after they have been added. This might change the overall value of the
solution. Consider if the values were 1.36 and 1.06. 1.36 rounds to 1.4 and 1.06 rounds to 1.1 and
adding them together results in the value 2.5. Yet this formula would add 1.36+1.05 resulting in
the value 2.41. This number rounded to the nearest tenth would result in 2.4 and not 2.5. This
implementation would be correct if the problem asked for the rounded sum of these values as
opposed to the sum of each rounded value.
Module 2 Course Notes
Module 2 Course Notes PF 106 Page 18
The correct answer requires both values to be rounded before they are added. Translating this
into Excel syntax results in the formula =ROUND(69.98,1)+ROUND(3.01,1).
Since the functions SUM, COUNT, COUNTA, AVERAGE, MIN, and MAX all work with any
number of arguments, a common mistake is to believe that functions like the ROUND function
work similarly. It is important to remember that Excel uses the comma (,) to delimit different
arguments and in functions like the ROUND function, Excel expects a specific number of
arguments in a specific order. Inserting too many arguments or changing the order of the
arguments can result in either an error message or an incorrect value. Remember that Excel can’t
“infer” what we want it to do if we don’t write our formulas obeying Excel’s syntax and rules.