Excel Formulas & Functions I

Excel Functions

Reference: Exploring Office 2013 Volume 1

Excel Chapter 2P. 456

See Excel Formulas II

Excel Statistical Functions

Statistical Functions

Function Syntax Description

=average( : ) Calculate the mean value of selected range

=count( : ) Tallies the number of cells in a range that contain values for use in calculations

=max( : ) Print highest value in selected range

=median( : ) Print midpoint value in selected range

=min( : ) Print lowest value in selected range

=sum( : ) Print total of all values in selected range

Know these formulas!

More Excel Statistical Functions

• Reference:

Exploring Office 2013 Volume 1Excel Chapter 2

P. 462

Inserting Functions

• Click in cell where you want to insert formula

• Click the Insert Function Button 𝒇𝒙

• In the Insert Function dialog box, type in name of function in search

• Click function

• Click OK

Cell References

• Relative cell reference• When a formula containing a

relative cell reference is copied, the cell references in the copied formula change relative to the position of the copied formula

• Absolute cell reference ($)• When a formula containing an

absolute cell reference is copied, the cell references in the copied formula have the same position of the copied formula

• Reference: Exploring Office 2013 Volume 1

Excel Chapter 2P. 448

Cell References

• A formula that references a cell can be moved to another cell• If we want to keep the result value

in the cell, we use a $ before the cell name

• You can cycle through cell references using F4 on your keyboard

Absolute Column Relative Column

Absolute Row

$(column name)$ (row number)

$A$1

(column name)$(row number)

A$1

Relative Row

$(column name) (row number)

$A1

(row number) (column name)

A1

AutoFill

• Click in cell

• Click the Fill Handle (square in bottom right corner)

• Drag formula down or across

• Release mouse button

Writing Formulas

• A formula in Excel must start with a =• you must select cells

• You need to give the formula a symbolOperation Similar Phrases Arithmetic Symbol Symbol in Excel

Addition total sum+ +

combined plus

Subtraction difference minus- -

less decrease

Multiplication product timesX *

of

Division ratio quotient÷ /

per

Exponentiation𝑥𝑛 ^

RANK.EQ() Function

• Gives the order of a number in a list.

• If you were to sort the list, the rank of the number would be its position in the list.

• Reference:Office Rank.EQ()

Number: 5 3 2 1

Position: 1 2 3 4

Create RANK.EQ() Function (step 1)

• Click in cell where you want RANK.EQ()

• Click the insert function button 𝒇𝒙

Create RANK.EQ() Function (step 2)

• In the Insert Function dialog box, Search for rank.eq

• Click Go

• Select RANK.EQ

• Click OK

Create RANK.EQ() Function (step 3)

• In the Function Arguments dialog box:

• Number: specific cell to rank

• Ref: the range of cells to rank(use absolute cell reference)

• Order: (not used)

• Click OK

Create RANK.EQ() Function (step 4)

• Use the fill handle to fill the function through the cell range