Formulas, Functions and References

1
Formulas, Functions and References
2
Formulas, Functions and References

• This lesson will cover
• Creating Simple Calculations
• Using Cell References
• Editing Formulas
• Inserting and Using Functions
• SUM, MAX, MIN, AVERAGE and MEDIAN
• COUNT and COUNTA
• IF
• Naming Cells and Cell Ranges

These topics are from Chapter 4 of the text...
3
Using Simple Calculations
Calculation is the process of computing formulas
and then displaying the results as values in the
cells that contain the formulas. Formulas can be
simple like (4 5)2 Or, they can contain
cell references (A2B5)2 Or, they can use
some of Excels built in functions PI()POWER(4,
2) you could also use 3.1416 (42)
Remember, that any values that are to be
calculated begin with the equal sign ()
4
Using Simple Calculations

• Since the next topic will be cell references we
  can start off easy. To use a simple calculation
• Click the cell in which you want to enter the
  formula.
• Type an equal sign (). This tells Excel that
  the following text is a formula.
• Enter the formula.
• Press the Enter key.
• For example, to use the formula (or calculation)
• 3.1416 (42), which would be the circumference
  of a circle with a radius of 4

5
Using Simple Calculations
Click on cell A1, then enter an equal sign ()
followed by the formula 3.1416(42) Note that
the same text is being entered into the actual
cell.
When you press the Enter key or click the green
checkmark on the formula bar The formula
remains in the Formula bar, but the value
displayed in cell A1 is now the result of the
formula 50.2656.
6
Using Simple Calculations

• Important things to remember about calculations
  in Excel workbooks
• Excel stores and calculates with 15 significant
  digits of precision, thats a number like
  100,000,000,000,000 or .000000000000001
• Excel calculates stored, not displayed, values.
  For example, a cell that displays a date as
  "6/22/2001" also contains a number that is the
  stored value for the date in the cell. That
  number is what is actually calculated if it is
  used in a formula.
• Excel cannot automatically calculate a formula
  that refers to the cell  either directly or
  indirectly  that contains the formula. This is
  called a circular reference. If a formula refers
  back to one of its own cells, you must determine
  how many times the formula should recalculate. A
  circular reference is not a good thing!

7
Using Simple Calculations
By default, Microsoft Excel automatically
recalculates formulas when the cells that the
formula depends on have changed. You can change
when the recalculation will occur
On the Tools menu, click Options, and then click
the Calculation tab. Under Calculation, select
an option.  Changing this option affects all open
workbooks. If you select an option other than
Automatic, press F9 to calculate all worksheets
in all open workbooks. To calculate only the
active worksheet, press SHIFTF9.
8
How to use References

• A reference identifies a cell or a range of cells
  on a worksheet and tells Excel where to look for
  the values or data you want to use in a formula.
  With references, you can
• use data contained in different parts of a
  worksheet
• use the value from one cell in several
  formulas.
• refer to cells on other worksheets in the same
  workbook,
• refer to cells in other workbooks
• refer to data in other programs
• References to cells in other workbooks are
  called external references.
• References to data in other programs are
  called remote references.

9
Relative References
When you create a formula, references to cells or
ranges are usually based on their position
relative to the cell that contains the formula.
When you copy a formula that uses relative
references, Excel adjusts the references in the
pasted formula to refer to different cells
relative to the position of the formula. Below,
cell B2 contains the formula A2 Excel displays
the value of cell A2 in cell B2. This is known as
a relative reference.
Relative Reference
10
Using Relative References
11
Absolute References
If you don't want Excel to adjust references when
you copy a formula to a different cell, use an
absolute reference. For example, if your formula
multiplies cell B2 with cell A1 (A1B2) and you
copy the formula to another cell, Excel will
adjust both references to match the new
locations. You can create absolute references to
the cells by placing a dollar sign () before the
parts of the reference that do not change, as
follows A1B2
Absolute Reference to cell A1 times a Relative
Reference to cell B2
12
Mixing Reference Types
You can mix relative and absolute references in
the same cell reference depending on what
reference you want to hold constant. Such
as A1B2
Absolute Reference to column A and Relative
Reference to row 1 times B2
13
Switching Between Reference Types
If you want to change relative references to
absolute (and vice versa), select the cell that
contains the formula. In the Formula bar, select
the reference you want to change and then press
F4. Each time you press F4, Excel toggles through
the combinations absolute column and absolute
row (C1) relative column and absolute row
(C1) absolute column and relative row (C1)
and relative column and relative row (C1). For
example, if you select the address A1 in a
formula and press F4, the reference becomes A1.
Press F4 again and the reference becomes A1, and
again it becomes A1.
14
Editing Formulas
To edit an existing formula Click on the cell
that contains the formula and then click in
the Formula bar to place your cursor in the
editing box. You can now edit the text just as
you would any other text. Remember to click the
check mark or press the Enter key when you are
done!
15
Editing Formulas
Note that the cells included in the formula
become highlighted in colors that match the color
of their reference in the Formula bar. The red X
next to the green checkmark is the Cancel
button. Use this if you have hopelessly messed
up a formulait happens!
The down arrow to the right of the red X on the
Formula bar displays a list of the recently used
functions. But that is another topic
16
Editing Formulas
When you are editing a formula, be careful not to
click out of the formula bar and on to the
worksheet. If you do you will add the address of
the cell you select (see the bottom figure).
This is good if you want to add the address to
your formula, and it happens by design. However,
for a novice Excel user this usually is the time
to click the Cancel button
17
Using Functions
Functions are predefined formulas that perform
calculations by using specific values, called
arguments, in a particular order, or structure.
For example, the SUM function adds values or
ranges of cells, and the PMT function calculates
the loan payments based on an interest rate, the
length of the loan, and the principal amount of
the loan. Arguments can be numbers, text,
logical values such as TRUE or FALSE, arrays,
error values such as N/A, or cell
references. The structure of a function begins
with the function name, followed by an opening
parenthesis, the arguments for the function
separated by commas, and a closing parenthesis.
18
Using Functions
For example SUM (A3, B5B10, C12) means add
the values in cells A3, B5 through B10 and
C12) A comma separates values and a colon
separates the beginning and end of a range of
cells. Syntax is another name for structure, so
the syntax of the SUM function is SUM(number1,
number2, ...) means and so on
19

• To Insert a function into a cell
• Select the cell where you want the function to
  display its results
• Select the Insert menu command from the main
  menu
• Select Function.
• This will display the Paste Function dialog

20
The Insert Function dialog box is really a wizard
of sortsat least the gateway to a wizardafter
it opens select a Function CategoryMost
Recently Used is selected here, it shows the
functions that were used recently by Excel. To
view the available functions select a category
from the list box and scroll through the function
names in the bottom list.
21
The POWER function is selected here.
As you highlight, or select, function names in
the list box an explanation of the Return value,
the Arguments and the Structure of the selected
function is displayed.
22
Notice that the wizard displays the result of the
function based on the current arguments.
Selecting the AVERAGE function displays the
function wizard. The wizard guesses which cell
references you want to use as the functions
arguments. If these are the correct cells click
the OK button, if not enter new ones. You can
enter a new cell or range of cells by selecting
them from the worksheet while the wizard is
displayed. This will change the values in the
wizard. If you click OK with the current
arguments
23
AVERAGE(E3E14)
see the average of the values from cells E3
through E14 displayed in cell F15.
24
this is in the wrong place, the Average should
be displayed in cell F15we need to cut and paste
the formula
25
AVERAGE(E3E14)
just like pasting any cell value...notice the
arguments didnt change.
26
Using FunctionsSUM

• Syntax SUM(number1,number2, ...)
• Number1, number2, ...   are 1 to 30 arguments for
  which you want the total value or sum.
• Examples
• SUM(3, 2) equals 5
• SUM("3", 2, TRUE) equals 6 because the text
  values are translated into numbers, and the
  logical value TRUE is translated into the number
  1 (False 0).

27
S
Using AutoSum

• The SUM function can be accessed through the
  Function wizard as shown earlier, and by using
  the AutoSum button on the Standard toolbar. To
  create a sum or total of a range of cell using
  this feature
• Select the cell where you want to insert the sum
  and click AutoSum
• Excel suggests a formula based on the cells
  above or to the right that contain numerical
  values.
• To accept the formula, press Enter.
• To change the suggested formula (cell
  references), select the range you want to total
  and press Enter.

The AutoSum button is located on the Standard
toolbar
28
AutoSum button
If you highlight cell E15 on this worksheet and
press the AutoSum button, Excel selects the range
of cells above it that contain numerical values
(E3 through E14). If this is what you want press
the Enter button on the formula bar to
confirm and enter the formula.
29
The SUM formula, SUM(E3E14), now exists in
cell E15
The result of the SUM formula is now displayed
cell E15
30
Using FunctionsMAX

• Syntax MAX(number1,number2,...)
• Number1,number2,...   are 1 to 30 numbers for
  which you want to find the maximum value.
• Examples
• If A1A5 contains the numbers 10, 7, 9, 27, and
  2, then
• MAX(A1A5) equals 27
• MAX(A1A5,30) equals 30

31
Using FunctionsMIN

• Syntax MIN(number1,number2, ...)
• Number1, number2,...   are 1 to 30 numbers for
  which you want to find the minimum value.
• Examples
• If A1A5 contains the numbers 10, 7, 9, 27, and
  2, then
• MIN(A1A5) equals 2
• MIN(A1A5, 0) equals 0

32
Using FunctionsAVERAGE

• Syntax AVERAGE(number1,number2, ...)
• Number1, number2, ...   are 1 to 30 numeric
  arguments for which you want the average.
• Examples
• If A1A5 is named Scores and contains the numbers
  10, 7, 9, 27, and 2, then
• AVERAGE(A1A5) equals 11
• AVERAGE(Scores) equals 11
• AVERAGE(A1A5, 5) equals 10

33
Using FunctionsMEDIAN

• Syntax MEDIAN(number1,number2, ...)
• Number1, number2,...   are 1 to 30 numbers for
  which you want the median.
• Examples
• MEDIAN(1, 2, 3, 4, 5) equals 3
• MEDIAN(1, 2, 3, 4, 5, 6) equals 3.5, the
  average of 3 and 4

34
Using FunctionsCOUNT
Syntax COUNT(value1,value2, ...) Value1,
value2, ...   are 1 to 30 arguments that can
contain or refer to a variety of different types
of data, but only numbers are counted Arguments
that are numbers, dates, or text representations
of numbers are counted arguments that are error
values or text that cannot be translated into
numbers are ignored
35
Using FunctionsCOUNT

• Using the cells shown on the right
• COUNT(A1A7) equals 3
• COUNT(A4A7) equals 2
• COUNT(A1A7, 2) equals 4
• Remember Only numbers, dates, or text
  representations of numbers are counted by this
  function.

The data type TRUE returns 1, FALSE returns 0,
be careful with non-numeric data types!
36
Using Functions COUNTA
Syntax COUNTA(value1,value2, ...) Value1,
value2, ...   are 1 to 30 arguments representing
the values you want to count. COUNTA considers
a value to be any type of information such as
text, dates, times, all number types, error
values and logical values (such as TRUE and
FALSE). It includes empty text ( ), but not
empty cells or arrays in its count. If you do
not need to count logical values, text, or error
values, use the COUNT function.
37
Using FunctionsCOUNTA

• Using the cells shown on the right
• COUNTA(A1A7) equals 6
• COUNTA(A4A7) equals 4
• COUNTA(A1A7, 2) equals 7
• COUNTA(A1A7, Two) equals 7
• COUNTA counts almost everything but empty cells
  and arrays

The error type DIV0! returns 2. The seven
main error types return values ranging from 1 to
7, so be careful with non-numeric data types!
38
Using FunctionsIF
Syntax IF(logical test, value if true, value if
false) Logical test is any value or expression
that can be evaluated to TRUE or FALSE. For
example, A10 100 is a logical expression if
the value in cell A10 is equal to 100, the
expression evaluates to TRUE. Value if true is
the value that is returned if logical test is
TRUE. Value if false is the value that is
returned if logical test is FALSE.
39
Using FunctionsIF
Example On a worksheet, cell A10 contains a
formula to calculate the current budget. If the
result of the formula in A10 is less than or
equal to 100, then the following function
displays "Within budget in the cell that
contains the function. Otherwise, the function
displays Over budget. IF(A10lt100,"Within
budget","Over budget")
40
Using FunctionsIF
Example In the example below, if the value in
cell A10 is 100, then the logical test is TRUE,
and the total value for the range B5B15 is
calculated. Otherwise, the logical test is FALSE,
and empty text ( ) is returned that blanks the
cell that contains the IF function. IF(A10100,SU
M(B5B15),"")
41
Defining and Using Cell Names
Naming cells (or ranges of cells) in Excel makes
it easier to navigate through your workbook and
to understand what the spreadsheet is trying to
present. You can use a text name to describe
what the cell represents or just make it easier
to select from the Name box. A descriptive
name can also be given to a formula to make it
easier to understand. For example, the formula
SUM(FirstQuarterSales) might be easier to
identify than SUM(Sales!C20C30). In this
example, the name FirstQuarterSales represents
the range C20C30 on the worksheet named Sales.
42
Defining and Using Cell Names
Selecting or Moving to a specifically named cell
can be accomplished from the Name box as shown
belowselect the name from the drop-down list and
that cell or range of cells will become active.
43
Defining and Using Cell Names

• To Define a cell name
• Select the cell or range of cells that you want
  to name.
• Click the Name box at the left end of the formula
  bar.
• Type in the name of the cell(s).
• Press Enter.

To name this cell
Type the name here
44
Defining and Using Cell Names

• To Define a cell name using the Insert menu
• Select the cell or range of cells that you want
  to name.
• Open the Insert menu
• Select the Name command
• Select the Define command

45
Defining and Using Cell Names

• When the Define Name dialog box appears
• Type in the name that you want to use.
• Press the OK button
• The Refers to text box shows the cell(s) that
  you are naming.

You can chose another cell or range of cells by
clicking the button at the end of the box. Names
can also be deleted from this dialog box by
clicking the Delete button.