# Formulas _ Functions

Document Sample

```					  MS Excel 2010

Outline
1-         Basics
2-         Formatting
3-         Formulas & Functions
7.1, 7.2 - If, Lookup Functions
9.1, 9.2 - Linear & Growth Trends, Loan & Investment Functions
10.1 - Goal Seek
4-         Charts
5-         Tables & Pivot Tables
6.1, 6.2 - Accessing Multiple Worksheets & Workbooks

3        - Formulas & Functions
7.1, 7.2 - If, Lookup Functions
9.1, 9.2 - Linear & Growth Trends, Loan & Investment Functions
10.1 - Goal Seek

Example 1 (“Formulas” worksheet)
Formulas
      Basic formulas

      Complex formulas
o Order of Precedence:      ( ), ^, *, /, +, -, Left-to-right

      Formula View
o Show Formulas
o Shortcut

#1 Rule: _________________________________________________________________________________________________

Example 2 (“Basic Functions” worksheet)
Basic Statistical Functions
      SUM
o AutoSum
      AVERAGE
      MEDIAN
      MIN & MAX
      ROUND
      COUNT
      COUNTA
      Nested Functions

Page 1 of 5
MS Excel 2010

Example 3 (“Dates” worksheet)
Basic Date Functions
   TODAY
   NOW
   YEAR
   MONTH
   DAY
   WEEKDAY
   Calculations with Dates

Example 4 (“RelativeRef”, “AbsoluteRef”, and “RunningSum” worksheets)

Types of Referencing
   Relative Reference

   Absolute Reference

   Mixed Reference

Example 5 (“IF1” and “IF2” worksheets)

Logical Functions
   IF

o   =IF(logical_test, [value_if_test_true], [value_if_test_false])

   Nested IF

o   =IF(logical_test, [value_if_test_true], IF(logical_test2, [value_if_test2_true], [value_if_test2_false]) )

Page 2 of 5
MS Excel 2010

Example 6 (“Lookup” worksheet)

Lookup Functions

   VLOOKUP
   HLOOKUP

=VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])

Lookup_value

Table_array

Column_index_number

Range_lookup

=HLOOKUP(lookup_value,    table_array,   row_index_number,   [range_lookup])

Row_index_number

Page 3 of 5
MS Excel 2010

Example 7 (“PMT” and “PPMT&IPMT” worksheets)

Basic Financial Functions
   Formulas tab | Insert Function
   PMT
   PPMT
   IPMT

=PMT (rate, nper, pv, fv, type)

Rate

Nper

PV

FV

Type

=PPMT (rate, per, nper, pv, fv, type)

=IPMT (rate, per, nper, pv, fv, type)
Per

Page 4 of 5
MS Excel 2010

Example 8 (“Goal Seek” worksheet)

What-If Analysis
   Goal Seek Purpose?
   How do you set it up?
o    Set Cell = the “GOAL CELL”
o    To Value = “DESIRED GOAL”
o    By Changing cell = the cell that Excel needs to determine

Example 9 (“Fill Growth Series” worksheet)

Projecting Future Values
   Linear (constant amount) and Growth (constant percentage) Trends
   Uses Autofill to fill in series of values
   Must know starting and ending (desired) values
   Home | Editing | Fill | Series…

Page 5 of 5

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 8 posted: 10/28/2011 language: English pages: 5
How are you planning on using Docstoc?