7-1 is 2101 spring 2010 chapter 8 using functions and tables
TRANSCRIPT
7-1
IS 2101Spring 2010
Chapter 8
Using Functions
And Tables
Objectives
• Use SUM, AVERAGE, MEDIAN, MIN, and MAX Functions
• Use COUNTIF and IF Functions, and Apply Conditional Formatting
• Use a Date Function• Freeze Panes and Create an
Excel Table
Objectives
• Format and Print a Large Worksheet
• Use Financial Functions• Use Goal Seek• Create a Data Table
Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions
• A function is a predefined formula that Excel has already provided.
• A function performs calculations by using specific values in a particular order or structure.
• Functions are grouped into categories such as Financial, Logical, etc.
Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions
Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions
Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions
• Functions can be inserted in several ways: Use the Function Library or Insert Function button. Use the Formula AutoComplete. Copy a similar function from another cell and edit the
arguments. Select Insert Function from any of the displayed menus
in the Function Library Group or press Shift + F3.
Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions
Use COUNTIF and IF Functions, and Apply Conditional Formatting
Use COUNTIF and IF Functions, and Apply Conditional Formatting
Use COUNTIF and IF Functions, and Apply Conditional Formatting
Use a Date Function
• The NOW function: retrieves date and time from your computer's calendar
and clock and inserts the information into the selected cell
result is formatted as a date and time places a sequential number in the cell that corresponds
to the date and time
Freeze Panes and Create an Excel Table
• The Freeze Panes command takes selected rows or columns and freezes them into place. The locked rows and columns become separate panes. A pane is a portion of a worksheet window bounded and
separated by other portions, such as vertical or horizontal bars.
Freeze Panes and Create an Excel Table
Freeze Panes and Create an Excel Table
Freeze Panes and Create an Excel Table
Format and Print a Large Worksheet
Use Financial Functions
• Interest = The amount charged for use of borrowed money.
• Interest percentage = Rate.• The initial amount of loan = Present value (Pv).
Use Financial Functions
• Principal = The total amount that a series of future payments isworth now.
• The number of time periods (# of payments) = nper.
• The value at the end of the time periods is Future value (Fv).
• The future value is usually zerofor loans.
Use Financial Functions
Use Goal Seek
• Goal Seek is a method to find a specific value for a cell by adjusting the value of one other cell. Goal Seek works backward from the desired outcome, to
find the input necessary to achieve the goal. Goal Seek is one of Excel’s What-If Analysis tools.
Use Goal Seek
Create a Data Table
• Data table: a range of cells that show how changing certain values
will affect the results makes it easy to calculate multiple versions in one
operation, and then view and compare the results
• A one-variable data table will change the value in only one cell.
• A two-variable data table will change the values in two cells.
Create a Data Table
Create a Data Table
Covered Objectives
• Use SUM, AVERAGE, MEDIAN, MIN, and MAX Functions
• Use COUNTIF and IF Functions, and Apply Conditional Formatting
• Use a Date Function• Freeze Panes and Create an
Excel Table
Covered Objectives
• Format and Print aLarge Worksheet
• Use Financial Functions• Use Goal Seek• Create a Data Table