Introduction to Excel 97

1
Introduction to Excel 97

• Information Technology Services
• User Training and Support

2
Mortgage Comparison Example

• Example of how you can quickly develop a
  worksheet that will help you make decisions
  
• Found a number of interest rates on the Web
• see www.bankrate.com
• Needed to compare to see which one was best

3
Objectives

• Describe features that are common to all
  electronic spreadsheets
  
• Define common spreadsheet terminology
• Create a worksheet in Excel, enter text and data,
  and enter formulas and functions.
  
• Format a worksheet by manipulating text,
  inserting comment boxes and formatting cells.
  
• Be able to analyze nested formulas and functions
  in a spreadsheet

4
Objectives (cont.)

• Be able to analyze logic statements in a function
  in a spreadsheet.
  
• Be able to formulate nested formulas and
  functions in a spreadsheet.
  
• Create, edit and save two Excel spreadsheets
  according to the handout.
  

5
What is an electronic spreadsheet?

• It is the electronic equivalent of an accounting
  worksheet, comprised of rows and columns to allow
  you to do almost any task in the organization of
  numbers in a clear, easy to understand format

6
Some Advantages of Spreadsheets

• Once it is set up properly, the user can save
  time by never having to set up the spreadsheet
  again
  
• Blank spreadsheets are called templates.
• Monthly salaries,grade sheets
• Spreadsheets are capable of exploring
  what-ifscenarios (e.g. budgets, submitting bids)

7
Spreadsheet terminology

• Row - horizontal axis (designated by numbers)
• Column - vertical axis (designated by letters)
• Cell - intersection of row and column (designated
  by an address comprised of the row number and
  column letter e.g. A1)
  
• Block//Range - a rectangular group of one or more
  cells (identified by block coordinates (e.g.
  A1..G4)

8
Spreadsheet terminology (cont.)

• Label - alphanumeric
• Value - a number or formula result
• Formula - creates relationships among other
  cells
  
• Template - a notebook that has labels, formulas,
  and all of the formatting but no actual data
  (e.g. actual figures and numbers)

9
Spreadsheet terminology (cont.)

• Worksheets - Excel stores data in files called
  worksheets (labeled notebooks in Quattro Pro)
  
• Popular Spreadsheets
• Quattro Pro 8.0
• Lotus 1-2-3
• Excel 97

10
Steps in Developing a Spreadsheet

• 1. Determining the purpose - what inputs, what
  outputs, what printed reports
  
• 2. Planning - plan it on paper first
• 3. Building and testing - make sure it
  manipulates the data correctly
  
• 4. Documenting - should include something within
  the worksheet itself (directions, name and date)

11
How do you enter formulas?

• - exponents
• - addition
• - multiplication
• / - division
• - - subtraction
• - function

12
Order Calculations are Performed

• First exponents
• Then any multiplication and division in the order
  they occur
  
• Then any addition and subtraction in the order
  they occur
  
• My Dear Aunt Sally

13
Example

• B5/B1 D3D42
• 1. Raise D4 to the second power
• 2. B5 is divided by B1
• 3. D3 is multiplied by step 1
• 4. Step 2 is added to step 3

14
Example

• 513
• 8

15
What is the effect of parentheses?

• Operations within parentheses are performed
  before those outside.
  
• Within the parentheses the basic rules are
  followed.
  
• Multiple sets of parentheses, the innermost are
  executed first followed by the next set.

16
Example

• Z1D2 (R3 E4F6 - (H6/F7 D32) A1)
• 1. D3 raised to the second power
• 2. H6 divided by F7
• 3. Step 1 added to step 2
• 4. E4 raised to F6
• 5. R3 plus step 4 minus step 3 plus A1
• 6. Z1 multiplied by D2
• 7. Step 6 plus step 5

17
Examples

• 423
• 11
• 4(23)
• 20
• (42)(35)4
• 40
• ((42)(35))4
• 64

18
What are built-in functions?

• Functions are pre-written formulas
• Functions must start with an equal sign
• Functions takes value(s), perform an operation,
  and returns a value(s)
  
• Values you use with a function are arguments
• AVERAGE(D3D7)
• AVERAGE is the function
• D3D7 is the argument

19
Developing a Spreadsheet

• Estimated fuel cost for a diesel tractor
• horsepower X .044 gals/hour/hp X hours X /gal
• 1. Determine purpose of spreadsheet
• To estimate fuel costs
• Inputs
• Outputs
• 2. Plan it
• Where does input and output go, formulas,
  functions, etc.
  
• 3. Build it
• 4. Documentation

20
Logical Operators

• - Greater than
• - Greater than or equal to
• - Equal to
• - Not equal to
• NOT- Logical NOT
• AND - Logical AND
• OR - Logical OR

21
Making Decisions with the IF Function

• The IF function is used when the value you want
  to assign to a cell is dependent on a condition.
  
• A condition is made up of two expressions and a
  logical operator
  
• Each expression can be a
• cell reference
• number
• text
• function
• formula

22
The IF Function
IF(B4B18,B17,0)
Value if false
Condition
Value if true
23
Examples

• IF(A5B7,A22-A3,G5E3)
• IF(E12/D5
• IF(SUM(A1..A5)10,1,0)
• IF(A12E2,A4D5,1)
• IF(A1D5
• IF(C5B5,VALID,INVALID)
• IF(AND(B12,A1100),Keep,Cull)
• IF(OR(A1B2,A1400),SUM(D1..D5),Yes)

24
Nesting a Logic Function
Nested Function
IF(CONDITION1,VALUE1,IF(CONDITION2,VALUE2,VALUE3))
Result if true
Condition
Result if false
25
Contact Information
User Training Support Mail Stop 9738 51 Magrud
er Mississippi State, MS 39762 662-325-0631 FA
X 662-325-5200
www.its.msstate.edu/training