uc excel 2010 - module 2 - calculations

2

DASH DESIGNS CONSULTING

Technology Training and Consulting Services

Microsoft Excel 2010Microsoft Excel 2010

Power Of Power Of Calculations Calculations

For For

M O

D U

L E

Excel 2010 Power of Calculations For The Haas School, UC Berkeley — Dash Designs Consulting

Microsoft Excel 2010

Power of Calculations

For

The Haas School of Business,

University of California

Copyrights and Trademarks

2011, Dash Designs Consulting, Jerry Maletsky

San Rafael, CA 94903

email: [email protected] web site: www.dashdesignsconsulting.com

fax (415) 491-1490

Any mention or use of Microsoft®, University of California, or any

third party products is hereby acknowledged by Dash Designs Consulting to be for the sole purpose of editorial and educational

use of this training manual and for the benefit of the mentioned


Dash Designs Consulting gives permission to the Haas School of

Business of the University of California at Berkeley to reprint this training manual for internal use only. No re-sale of this material or

renunciation of copyrights are granted by this author.

Revised: August 7, 2011


Table of Contents

Calculation Fundamentals ............... 2

Excel Core Functions ...................... 4

Functions That Include Text .......... 12

Insert Function Dialog Box ............ 18

Creating Absolute References ........ 20

Named Ranges In Formulas ........... 22

Calculating Across Worksheets ....... 26

AutoCalculate Feature .................. 28

Checking Errors in Calculations ...... 29

Formula Auditing Tools ................. 30

Reference Workbook: UC Excel 2010-Calculations Workbook.xlsx

Excel 2010 Power Of Calculations Excel 2010 Power Of Calculations

C H

A P

T E

R

Excel 2010 Power of Calculations For The Haas School of Business, UC Berkeley - Dash Designs Consulting 1

1 Microsoft Microsoft

Excel 2010 Excel 2010

Power of Power of

CalculationsCalculations

ForFor

Jerry Maletsky

Dash Designs Consulting

Technology Training and Consulting

C H

A P

T E

R


1 Calculation Fundamentals

Creating Simple Calculations

Calculations are used to perform mathematical operations on values

entered into the cells of the worksheet. The addresses of those cells (i.e., B5) and the appropriate mathematical operator are used to convey the

instructions on what and how to calculate in Excel.

All calculations begin with an equal sign (=). The equal sign tells

Excel that an instruction to perform a calculation is following.

You enter the formula into the cell in which you want the result to appear. Since the calculations are based on the values in those cell addresses,

they automatically update as the source values are changed.

When calculations contain more than one operation, parentheses are required in order to calculate accurately. For example, if you wanted to

calculate A5+A7 and then multiply that times the value of D6, the formula

should be written as (A5+B7)*D6.

The Hierarchy of Mathematics

Operations in Parenthesis

Exponentiation

Multiplication

Division

Addition

Subtraction

Reference Worksheet: Regional Sales

C H

A P

T E

R


Calculation Fundamentals

1

Mathematical Operator Mathematical Outcome

+ (Plus Sign) Addition

- (Minus Sign) Subtraction

* (Asterisk) Multiplication

/ (Slash) Division

( ) (Parentheses) Separates the order of

mathematical operations. Calculations in parentheses are

performed separately.

% (Percent) Converts numbers into percent

(i.e., 5.7%)

^ (Caret) Used for Exponentiation.

(i.e., 10^3 which is 10*10*10).

Creating Simple Calculations

Examples of Simple Calculations

=A7+B12 Adds value in cell A7 to value in cell B12

=A7-D6 Subtracts value in cell D6 from value in cell A7

=A7*J5 Multiplies value in cell A7 times value in cell J5

=H12/M34 Divides value in cell H12 into value in cell M34

=(A5+B7)*D6 Adds value in cell A5 to value in B7 and takes that

result and multiplies it times value in cell D6

C H

A P

T E

R


Creating Calculations With Functions

Functions are built-in shortcuts for creating calculations. There are hundreds of

functions in Excel. Each has its own unique purpose in calculating values.

Functions simplify and shorten the methods in which we calculate.

As with all calculations, functions begin with an equal sign, followed by the name of the function and a pair of parentheses. Inside the parenthesis, the arguments for that function are placed. Arguments can be a variety of information. It can

simply be a reference to a cell range. For example, =Sum(B5:B35).

There are a group of functions that perform basic calculations that are created in

the same manner as the Sum function. They include:

Sum Min Count

Average Max StDev

Examples are:

=Sum(B5:B35) Adds the values in the cell range B5 through B35

=Average(B5:B35) Calculates the average value in the cell range B5

through B35

=Min(B5:B35) Finds the lowest value in the cell range B5

through B35

=Max(B5:B35) Finds the highest value in the cell range B5

through B35

=Count(B5:B35) Counts the number of cells that contain only numbers in the

cell range B5 through B35

=StDev(B5:B35) Estimates standard deviation based on a sample.

Standard deviation is a measure of how widely values are

dispersed from the average value (the mean).

Reference Worksheet: Core Functions

Excel Core Functions 1

Excel 2010 Formula Tab

C H

A P

T E

R


Calculating With The AutoSum Button

Excel has a great shortcut to use when you want to add values in cells. It

is the AutoSum button on the Standard Toolbar.

Simply click in a blank cell just below the values you want to add (or just

to the right of the cells you want to add) and click the AutoSum button. Excel will automatically create the calculation with all the proper syntax (i.e. =Sum(A5:A12)). All you have to do is verify that Excel has included

the correct cells you want to calculate and then click the AutoSum button

again. This will finish the formula.

Note: Even if Excel does not select the correct cell range you can simply replace the incorrect range with the correct cells by selecting the appropriate cells with your mouse and then clicking the AutoSum button

to finish the calculation.

Excel Core Functions

1

AutoSum Button

on the Formulas

Tab

AutoSum Button

on the Home Tab

C H

A P

T E

R


Calculating With The AutoSum Menu

The AutoSum menu contains shortcuts to the basic functions that have

the same syntax (that is, they are written the same way) as the Sum function. The other basic functions, Average, Count, Min, and Max are

listed here. Other functions can be accessed by clicking the “More

Functions” option on the AutoSum menu.

Steps:

Click into the blank cell in which the result is to appear

Click the AutoSum menu and select the appropriate function

Verify that the correct cell range has been selected or select the

correct cell range with your mouse

Click the Checkmark on the

Formula Bar or press Enter



Note:

In Office 2010, some

command buttons con-

sists of two parts. One

part (the top half) exe-

cutes the default option

(in this case, Sum) the

bottom half displays a

list of more options (in

this case, the core

functions)

1

C H

A P

T E

R


Calculating With The Average Function

The Average function automatically finds the average value within a

selected range of cells. That is, it adds all the values in the specified cell range and divides by the number of cells that had a value in it. The

Average function does not include empty cells or cells that have text

in them. It does however, include zero in its calculations.

If you want to include text as part of your average, use the AverageA

function. Text is then calculated as a zero value

Note: Even if Excel does not select the correct cell range, you can simply

replace the incorrect range with the correct cells by selecting the appropriate cells with your mouse and then clicking the checkmark (see

steps on previous page) on the Formula Bar or by pressing Enter.


1

C H

A P

T E

R


Calculating With The Max Function

The Max function automatically finds the highest value within a selected

range of cells. The Max function does not include empty cells or cells

that have text in them. It does however, include zero in its calculations.

If you want to include text as part of your calculation use the MaxA







1

C H

A P

T E

R


Calculating With The Min Function

The Min function automatically finds the lowest value within a selected

range of cells. The Min function does not include empty cells or cells

that have text in them. It does however, include zero in its calculations.

If you want to include text as part of your calculation use the MinA






1

C H

A P

T E

R


Calculating With The Count Function

The Count function automatically counts the number of cells within a

selected range of cells. That is, if the specified cell range contains six cells and five of them have numbers in them, the result is five (5). The Count

function does not include empty cells or cells that have text in them. It

does, however, include zero in its calculations.

If you want to include text as part of your count, use the CountA

function. Cells with text are counted as well as cells containing numbers.






1

C H

A P

T E

R


Calculating With The STDEV Function

The STDEV function estimates standard deviation based on a sample. The

standard deviation is a measure of how widely values are dispersed from

the average value (the mean).

STDEV assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the standard

deviation using STDEVP.

The standard deviation is calculated using the "unbiased" or "n-1"

method.

StDev uses the following formula:

If you want to include text as part of your count, use the STDEVA function. Cells with text in are counted as

well as cells containing numbers.

Note: Even if Excel does not select the correct cell range, you can simply replace the incorrect range with the correct cells by selecting the

appropriate cells with your mouse and then clicking the checkmark (see


X is the sample mean Average

(number1,number2,…) and n is the

sample size.

See Page 18 for

instructions on finding

other functions not

listed in the AutoSum

button.


1

C H

A P

T E

R


Calculating With Functions That Include Text Entries

Excel provides a group of functions that will calculate all values in a

specified cell range including numbers and text.

These functions are denoted by an “A” at the end of the function name.

For example, CountA will count the number of non-empty cells within a

selected range of cells.

Here are some examples:

Even if Excel does not select the correct cell range, you can simply replace the

incorrect range with the correct cells by selecting the appropriate cells with your mouse and then clicking the checkmark (see below) on the Formula Bar or by

pressing Enter.

Function Example Description

AverageA =AverageA(A1:A20) Returns the average of the labels

and values in cell range

MaxA =MaxA(A1:A20) Returns the highest value in cell

range

MinA =Min(A1:A20) Returns the lowest value in cell

range

CountA =CountA(A1:A20) Returns the number of cells in

range (not blank)

StDevA =StDevA(B5:B35) Estimates standard deviation

based on a sample.

Functions That Include Text Entries

Reference Worksheet: Functions With Text 1

C H

A P

T E

R


Calculating With The CountA Function

The CountA function automatically counts the number of non-empty cells

within a selected range of cells. That is, if the specified cell range contains six cells and they either contain numbers or text entries, the result is six

(6). The CountA function does not include empty cells. It does

however, include zero in its calculations.

In order to access the CountA function, you will need to use the “More

Functions” option on the AutoSum menu. Then, in the Insert Function Box, locate the CountA function and fill in the Arguments

dialog box with the correct cell range.





1

C H

A P

T E

R


Evaluates the text as zero (0)

Calculating With The AverageA Function

The AverageA function calculates the average of its arguments, including

numbers, text, and logical values. AverageA evaluates text as a zero (0). The AverageA function does not include empty cells. It does,

however, include zero in its calculations.

In order to access the AverageA function, you will need to use the “More Functions” option on the AutoSum menu. Then, in the Insert

Function Box, locate the AverageA function and fill in the Arguments

dialog box with the correct cell range.

Note: Even if Excel does not select the correct cell range, you can simply replace the incorrect range with the correct cells by selecting the appropriate cells with your mouse and then clicking the checkmark (see



Reference Worksheet: Functions With Text 1

C H

A P

T E

R


Calculating With The MaxA and MinA Functions

The MaxA and the MinA functions calculate the highest (MaxA) or the

lowest (MinA) of its arguments, including numbers, text, and logical values. MaxA and MinA evaluate text as a zero (0). The MaxA and MinA

functions do not include empty cells. They do however, include zero in

its calculations.

In order to access the MaxA or the MinA functions, you will need to use

the “More Functions” option on the AutoSum menu. Then, in the Insert Function Box, locate the MaxA or MinA function and fill in the

Arguments dialog box with the correct cell range.




MaxA and MinA evaluate the text as zero (0)


1

C H

A P

T E

R


Median Function


The Median function will return the middle value within a group of val-

ues. For example, given the values 2, 3, 5, 7, and 10 the median value would be 5 since there are 2 values less than the middle value and

2 values higher than the middle value. If there is an even number of values in the cell range, then MEDIAN returns the average of the two

numbers in the middle.

Note: Even if Excel does not select the correct cell range, you can simply replace the incorrect range with the correct cells by selecting the appropri-

ate cells with your mouse and then clicking the checkmark (see steps on

page 6) on the Formula Bar or by pressing Enter.

In the example below, the Median function returned a value of $62,500

because that value falls at the midpoint of the values in the cell range.

This can be an advantage in determining a trend in data.

Finding the Middle Value With The Median Function

1

C H

A P

T E

R


Mode Function

Find the Most Frequent Occurring Value

The Mode function allows you to find the most frequently occurring value in a cell

range. This can provide useful information in establishing a trend in your data. For example, if within a 12 month period the most common number of units sold for a

given product is 25, then that might help you establish a more advantageous minimum order value. The Mode function analyzes a given cell range and looks for the most repetitive value used. If there are no repetitive values, then the result will

return #N/A which is an error message meaning not applicable.

Example: =Mode(B7:E18)

Reference Worksheet: Core Functions 1

C H

A P

T E

R


Calculating With The Insert Function Dialog Box

The Insert Function Dialog Box is an automated method to create a calculation

using any function within Excel. It enters all the proper syntax. That includes the equal sign (=), the proper spelling of the function name, as well as the

appropriate commas (separating arguments) and the proper parentheses

(i.e. =Sum(B5:B35) ).

Note: Even if Excel does not select the correct cell range you can simply replace

the incorrect range with the correct cells by selecting the appropriate cells with your mouse and then clicking the checkmark (see steps on page 6) on the

Formula Bar or by pressing Enter.


Insert Function Dialog Box

Steps:

Click into the blank cell in which the result is to

appear

Click the AutoSum menu (Formula Tab) and

select “More Functions” option

Select the All category (if necessary)

Click into the main list of functions

Type the first few letters of the function

name (i.e., Cou for CountA)

Scroll appropriately to locate the proper func-

tion and double-click

In the Function Arguments dialog box, enter the appropriate argu-

ments (i.e., the cell range) for what you wish to calculate

Click the OK button to complete the process

1

C H

A P

T E

R



Click into main

function list and type

the first few

characters of the

function name. Then

scroll to locate

appropriate function.

Select the Category

Fill in the appropriate

required (bold)

argument(s) (i.e.,

select the correct cell

range). Then click OK

to complete the

process.


If you want to edit the

function later on using the

Function Argument dialog

box simply click on the

result cell and click the

function button on the

Formula toolbar

Great Tip!

A shortcut to finding a

function is to type the

name in the search box

and click Go

1

C H

A P

T E

R


Creating An Absolute Reference When Copying A Formula

By default, when a calculated cell is copied, the references in that formula are

relatively adjusted for the new location. This process is known as Relative Reference. When copying a formula, the result value is not being copied but

that formula's instructions. For example, if the cell that contains the formula =Sum(B5:B10), is copied to the next column to the right, that instruction will be pasted as =Sum(C5:C10). Excel relatively adjusts the formula’s cell references

to reflect its new location.

There are instances when copying a formula, that part or all of the cell reference

in that formula should not change when pasted in its new location. This process is known as Absolute Reference. Absolute References always refer to the same

original cell reference in that formula.

For example, in a worksheet column, a formula is created to calculate the percentage of sales of each product to the total sales for all products (=F6/F20).

That calculation is to be copied down to adjacent cells in that column. In that instance, it would be necessary to keep the reference to the total cell (F20) constant so that its reference does not relatively change as that formula is copied

down that column. This is accomplished by placing a dollar sign ($) before the column letter and the row number of the total cell reference in that formula

(=F6/$F$20).

The dollar sign can be typed manually. More efficiently, the function key <F4> can be pressed after typing the cell reference in the formula. This will ensure that the dollar signs are placed correctly. In fact, there are references known as

Mixed References where just the column letter or just the row number are Absolutely Referenced. Pressing the <F4> key will cycle through the levels of

Relative/Absolute references to that cell reference in the formula.

Great Tip!

The <F4> key is a

shortcut for

placing an

Absolute

Reference in

a formula.

Cell Reference Reference Type Description

F20 Relative Row and Column are relatively adjusted

when formula is copied.

$F$20 Absolute Row and column reference stay constant

when formula is copied.

F$20 Mixed Column reference is relative adjusted.

Row Reference will stay constant when

formula is copied.

$F20 Mixed Row reference is relative adjusted.

Column Reference will stay constant when

formula is copied.

Reference Worksheet: Absolute References

Creating Absolute References 1

C H

A P

T E

R


Creating Absolute References

Creating An Absolute Reference When Copying A Formula

Steps:

Enter the specified cell reference into the calculation

Press the <F4> key (until the required reference is set)

Enter the remainder of the calculation (if necessary)

Press <Enter> to complete the calculation

In this example, when the

formula in C6 (=B6/$C$2) is copied down to the end of the

“Commissions Paid” column, the cell reference to the Commission (C2) will stay

constant but the reference to the “Total Revenue” (B6) will

change to B7, B8, B9, etc.

1

C H

A P

T E

R


Defining A Named Range

You can assign a name to a cell or cell range. The name can be used as a

navigational tool, that is, to go to that assigned name, or the name can be used as a replacement for that cell range in a calculation. This can be

a very efficient way of creating formulas with less chance for error. In addition, named ranges are Absolute References so that if you copy

the formula, that part of the formula will stay constant.

You can assign a name to an existing cell or cell range, a value that doesn’t have to exist in a cell, or a formula that doesn’t have to exist in a

cell.

Name Box

RULES FOR ASSIGNING NAMES

Names must start with a letter or an

underscore (not a number)

Names cannot contain spaces (use

underscores for spacers)

Names can be up to 255 characters

long

Names are not case-sensitive

Don’t use cell references or commands

as names

Reference Worksheet: Naming Cells

Using Named Ranges In Formulas 1

C H

A P

T E

R


Using Named Ranges In Formulas

Defining A Named Range

Steps:

Select the cell or cell range you want to name

Click into the Name Box (upper left corner of screen)

Type a name (no spaces)

Press Enter (Very Important!)

Note: You can only define a name in the Name Box. To edit or delete that named range

you must use the Formula Tab: Name Manager command.

1

C H

A P

T E

R


Reference Worksheet: Naming Cells


1

Inserting Names In Formulas

Names can be inserted into a formula by typing in that required name at

the appropriate place while creating the formula. A more efficient meth-od, which doesn’t require the user to remember how to spell the name, is

to insert the name using the “Use in Formula” command list in the De-

fined Name Group on the Formula Tab.

In addition, the user can press the Function Key <F3> to display the Paste

Name dialog box on the screen when the name is to be inserted in the for-

mula.

Press the Function Key

<F3> to display the

Paste Name dialog box

“Use In Formula” com-

mand on the Formula Tab

C H

A P

T E

R



1

Example of a named range in a formula!

Inserting Names In Formulas

C H

A P

T E

R


Calculating Across Worksheets


Calculations can be performed on one worksheet that reference values on

other worksheets in that or other workbooks. These types of calculations are called 3-D Formulas. As an example, a user can be tracking regional

sales data on separate worksheets and “collect” all that data on a

summary worksheet.

Calculations can be performed either using direct cell references (‘US

Sales’!B7+Europe!B7+Asia!B7+Australia!B7) or by using functions

(=Sum(‘US Sales:Australia!B7’).

In the above example, Excel sometimes requires apostrophes (‘) around worksheet names that contain spaces and an exclamation point (!) after the worksheet name to separate it from the cell references. An efficient

method of ensuring the use of the correct syntax is by using the mouse

to select the cells referred to in that formula.

Steps:

Click on the cell on the worksheet in which you want to

display the result of the formula

Type an equal sign (=) or…

Click the AutoSum button or…

Click the Insert Function button

Select the first worksheet and the cell or cells that

contain the data to be calculated

Hold Shift key and select the last worksheet to include in that

calculation

Press the Enter key or click checkmark on the Formula Bar

Arrange the

worksheets so

that all the

worksheets to

be included in

the formula are

in consecutive

order so that it

is easy to select

them!

Great Tip!

Reference Worksheet: All Regions

Supporting Worksheets: US Sales, Europe, Asia,

South America, Australia

1

C H

A P

T E

R




Note:

In the example

to the right,

once the

calculation is

complete, the

formula cell

can be copied

down and then

across by using

the AutoFill handle.

1

C H

A P

T E

R


Analyzing Data With The AutoCalculate Feature

The AutoCalculate feature automatically appears on the Status Bar when

you select two or more cells that contain numbers. It provides temporary results that are not placed in any worksheet cell nor will it be printed. The

AutoCalculate functions include Sum, Average, Min, Max, and Count.

They are accessed by Right-Clicking on the AutoCalculate location.

AutoCalculate Feature

AutoCalculate

Functions can be added to

AutoCalculate by Right-Clicking on

the Status Bar and selecting the

function to add

Reference Worksheet: Core Functions 1

C H

A P

T E

R


Checking Errors in Calculations

Checking Errors in Calculations

Excel automatically checks your formulas as you alter the worksheet based

on a pre-set group of rules. If a formula no longer adheres to those rules, Excel displays an error indicator in the cell and a “Smart Tag” icon

adjacent to that cell.

The error indicator appears as a green triangle in the top left corner of

the cell.

A common error is when additional data is added to the worksheet and the formula cell does not update to include that data. Excel gives you the

option of updating that formula in the error-checking Smart Tag.

Above formula (=Sum(B7:B12)does

not include top value cell (B6)

Reference Worksheet: Error Checking Worksheet 1

C H

A P

T E

R


Using The Auditing Toolbar

Excel provides a tool called the Auditing Toolbar in which formulas can be

traced back to their precedent cells. Value cells can be traced to their dependent formula cells. With the Auditing Toolbar, Tracer Arrows are

graphically drawn on the worksheet to and from the formula cells. If left

activated, they will be printed when the worksheet is printed.

Auditing Terms:

Cell Precedent: a cell address that is being referenced in a formula. For example, in the formula, =Sum(B5:B85), the cell range

B5:B85 would be called the precedent cells.

Cell Dependent: the formula cell which is referencing other cells for its result. For example, in the formula, =Sum(B5:B85), the

result cell (i.e. B90) would be called the dependent cell.

Tracer Arrows: graphic arrows that show the direction of the data flow to

or from a formula cell. There are 3 types of tracer

arrows:

Blue - Formula Tracer Arrow

Red - Error Tracer Arrows Dashed Black - External references

(i.e. another worksheet)

Double-Click To Go To Precedent Cells

Formula Auditing Tools Reference Worksheet: Auditing Formulas 1

C H

A P

T E

R


Tracing Precedent Cells

Formula Auditing Tools

Reference Worksheet: Auditing Formulas 1

Steps:

Select the formula cell to be evaluated

On the Formulas Tab, Formula Auditing Group

Click Trace Precedents button

Click again to see indirect levels of Precedents

To remove arrows, click Remove Arrows

C H

A P

T E

R


Tracing Dependent Cells

Reference Worksheet: Auditing Formulas


1

Steps:


On the Formulas Tab, Formula Auditing Group

Click Trace Dependents button

Click again to see indirect levels of Precedents

To remove arrows, click Remove Arrows

C H

A P

T E

R


Using The Auditing Toolbar Formula Evaluation

More complex formulas can be evaluated one step at a time in the order in

which the formula is calculated. The Evaluate Formula button on the Audit-

ing Toolbar can accomplish this task.

Steps:


On the Formula Tab, Auditing Group, Evaluate Formula button

To evaluate each part of the formula, click Evaluate button

To precedent steps of the formula, click Step In button

To evaluate the next step of the formula, click Step Out button

When complete, click Close button



C H

A P

T E

R


Viewing/Printing Worksheet Formulas

Formula Tab: Auditing Group: Show Formulas Command

One method of documenting a worksheet is to view its formula

instructions. The Formula Tab: Auditing Group: Show Formulas command will replace the worksheet’s formula results with the instructions

in the cells. When the worksheet is printed in this mode, formula instruc-

tions will print in place of their results.



Show Formulas - Before

C H

A P

T E

R


You can toggle between Formula

Mode and Normal Mode by

pressing:

<Ctrl>+ ~ (Tilde)

Viewing/Printing Worksheet Formulas

Worksheet Displayed With Formulas


1

C H

A P

T E

R


Notes

uc excel 2010 - module 2 - calculations

Documents

dash designs

cell range

cell range

correct cell

formula auditing

insert function

show formulas

arguments