Introduction Chapter 1 Applying Fundamental Excel Skills and Tools in Problem Solving

1
IntroductionChapter 1Applying Fundamental
Excel Skills and Tools in Problem Solving
2
The Problem Solving Process

• We apply a systematic approach to problem solving
• Problem identification
• Analysis and design of possible solutions and
  selection of the best solution
• Solution implementation and testing
• Post-implementation evaluation and audit

3
Problem Identification

• Correctly identifying and understanding a problem
  is important
• If you dont understand the problem, you cannot
  solve it
• Applications that do not solve a problem are
  useless

4
Problem Analysis and Design

• Explore alternatives to a problems solution
• Select the best solution to the problem
• Model the solution
• UML diagrams
• Data flow diagrams
• Entity Relation diagrams
• Select appropriate technologies

5
Solution Implementation and Testing

• Here, the solution to the problem is implemented
  according to the design specifications
• Once implementation is complete, testing begins
• You can never test too much

6
Solution Evaluation

• Once the system is deployed, assess whether the
  project was successful
• Did the solution meet is objectives?
• Are the users satisfied with the results?
• Was the problem correctly solved?
• Are there any deficiencies?

7
Who Uses Excel?

• Accounting
• Financial estimates
• Trend analysis
• Goal seeking
• Everybody creates charts
• Sales and marketing
• Capital budgeting

8
Excel Workbooks and Worksheets

• A physical .XLS file corresponds to a workbook
• An .XLS file contains one workbook
• A workbook contains one or more worksheets
• By default there are three
• Reorder worksheets by dragging the worksheet tab
• Right-click on the worksheet tab to rename, add,
  change, and delete worksheets
• A workbook must have at least one worksheet
• Create and name worksheets before creating
  formulas because the worksheet name is significant

9
File Changes to Excel 2007

• Excel files are .xlsx files instead of .xls files
• Excel 2007 files are NOT backward compatible with
  Office 97-03
• Run the Compatibility Checker from the Office
  menu
• Save in Office 97-03 format
• For Office 03 users, download the backward
  compatibility libraries from microsoft.com

10
The Default Workbook (Illustration)
Three worksheets
11
Designing a Worksheet (Introduction)

• Worksheet design is not an accident
• Input area(s) should be visually separated from
  the output area(s)
• Input areas and output areas may be placed on
  separate worksheets
• Column names should appear for columnar data
• Row names are also appropriate
• Input values and output values should have
  prompts
• Format data to improve readability
• Protect worksheet regions to prevent user errors
  and accidental erasure

12
Designing a Worksheet (Best Practices)

• The design decision is often a subjective one
• For example, should time-series data be organized
  in a row-wise or column-wise fashion
• How to organize data depends on the questions
  being asked
• Its common to create a summary worksheets with
  data from the other worksheets
• Worksheet tabs can organize region or time series
  data

13
Designing a Worksheet (Formatting 1)

• Numeric data should be right justified while text
  data should be left justified
• Format the number of decimal places as
  appropriate
• Select a currency symbol and use thousands
  separators for money amounts
• Use boldface and color for emphasis
• Modify the column widths as necessary
• Drag the column separator to increase or decrease
  the width
• Pound signs appear when the data will not
  fit within the column

14
Designing a Worksheet (Formatting 2)

• The Formatting toolbar contains common formatting
  commands
• Select the cell, row, or column to format and
  select the desired formatting characteristic

15
Designing a Worksheet (Formatting 3)

• Use the Format Cells dialog box for additional
  formatting options

16
Format Cells Dialog Box (Tabs 1)

• Number Use to set precision and separators
• Note that there are several predefined formatting
  types
• Custom formats can also be created using special
  placeholder characters
• Alignment Use to change the position of a
  cells contents
• Use to rotate text
• Its possible to indent a cells contents
• Font Change the font and font attributes

17
Format Cells Dialog Box (Tabs 2)

• Border Add or remove cell borders. All four
  sides can be control individually. Set border
  style and cell color
• Creating borders can lead to some nice output
  formatting
• Single and double underscores for the accountants
• Patterns Apply background patterns to cells
• Its also possible to define the cells
  background color here
• Protection Prevent the user from modifying a
  cells contents
• The worksheet must be protected for this option
  to have any effect
• Use this tab to hide a cells contents from the
  user

18
Excel 2007 Formatting

• Excel 2007 has some nice new formatting templates

19
Common Worksheet Errors (Introduction)

• Formatting errors
• Text is truncated
• Numeric values overflow and cannot be displayed
  properly
• Calculation errors
• Formulas are syntactically incorrect
• Formulas are syntactically correct but contain
  logic errors

20
Common Syntax Errors

• From Table 1.1 on page 21

21
Excel Formulas (Introduction)

• A formula is an equation consisting of Functions,
  operators, and operands
• Operands consist of constants or cell references
• Functions begin with an equals sign ()
• Worksheet format
• Click Ctrl-graveaccent to switch between formula
  view and formula result view

22
Excel Formulas (Worksheet View)
23
Excel Formulas (Arithmetic Operators)

• Table 1.2 lists the arithmetic operators

24
Operators (Precedence)

• Operators are evaluated in a predefined order
  known as precedence
• Exponentiation
• Multiplication and division from left to right
• Addition and subtraction from left to right
• Use parenthesis to override the default
  precedence order
• See table 1.3

25
Cell References (1)

• Formula operands are made up of constants and
  cell references
• Avoid the use of constant values for input data
• Use cell references and have the user enter the
  input values
• To reference an individual cell, specify the
  column name followed by the row number as in
• A1 (Column A row 1)
• C7 (Column C row 7)

26
Cell References (2)

• A range of cells is referenced by defining the
  upper-left and lower-right cell to form a
  rectangle
• A rectangle may be one cell wide (one column) or
  one row high (one row)
• A full colon ( ) separates the two cells that
  mark the rectangles bounds
• Examples
• A1B2 (A1 through B2 4 cells in two rows and
  two columns)
• A1A10 (A1 through A10 10 cells in one column)
• A1C1 (A1 through C1 three cells in one row)

27
Cell References (3)

• To reference cells on other worksheets, the
  worksheet name followed by the exclamation point
  must precede the cell reference as in
• Sheet1!A1 (The cell A1 on Sheet1)
• Sheet1!A1B2 (The cells A1 through B2 on the
  sheet named Sheet1)

28
Cell References (4)

• Its possible to select an entire column
• Its possible to select an entire row
• Examples
• DD Selects column D
• 11 Selects row 1
• Its possible to select non-contiguous ranges
  (more later)

29
Absolute and Relative Cell References
(Introduction)

• Be default, Excel creates relative cell
  references
• When a cell is copied, the formula in the cell is
  adjusted based on the row and column displacement
• Its also possible to create absolute cell
  references
• The cell of an absolute cell reference will not
  be adjusted when copying
• Mixed cell references consist of one absolute row
  or column and one relative row or column
• Absolute reference is marked by a dollar () sign
  in the row or column

30
Absolute and Relative Cell References (Examples)

• A1 A relative reference
• A1 An absolute reference
• A1 Mixed reference
• (absolute column, relative row)
• A1 Mixed reference
• (relative column, absolute row)
• Press F4 while editing to change between the four
  types of references

31
Absolute and Relative Cell References (Best
Practices)

• Use Absolute references for constant values
• Dont use literal values in formulas as they are
  hard to locate
• Use mixed references when working with constant
  data in a row or a column
• This way several rows and columns can be copied

32
Named References

• Its possible to give a cell or range of cells a
  name
• To create a named reference
• Select the cell or cells in the range
• Enter the name in the Name combo box
• The name must begin with a character
• Duplicate names are not permitted

33
Named References (Creating)
Create or select name
Named reference
34
Named References (Using)
Operand contains a named reference
35
Excel Functions (Introduction)

• Just as Access has intrinsic functions so too
  does Excel
• Functions accept arguments used to send data to
  the function
• Arguments appear in a list bounded by parenthesis
• Each argument in a list is separated by a comma
  (,)
• The formula begins with an equal sign ()
• Example, add the values of rows 1 through 8 in
  column A
• The function accepts one argument a range
• SUM(A1A8)

36
Creating Functions

• There are different ways to create a function
• Enter the function manually in the Function
  editor
• Intellisense technology supplies ToolTips for the
  function arguments
• Use the Insert Function Dialog box
• Select functions by category
• Use a window to insert the function arguments

37
Excel 2007

• Formulas tab makes it easy to locate formulas

38
Insert Function Dialog Box
Select function category
Selection function
39
Entering Function Arguments
Select field ranges
Fill-in the fields for the function arguments
40
Common Excel Functions (AVERAGE)

• Calculate the average of a list of values
• Only numeric values are included in the average
• Cells containing text or nothing are ignored
• Use a range or ranges to specify a list
• Use AVERAGEA

41
Common Excel Functions (Average Example)
42
Common Excel Functions (MIN, MAX)

• Calculates the minimum value or maximum value
  from a range of values
• Cells containing text that cannot be converted
  will cause an error

43
Common Excel Functions (MIN Example)
44
Common Excel Functions (COUNT)

• Counts the number of items in a list
• Empty cells are ignored
• Text cells are ignored
• Note that COUNTA includes textual cells instead
  of excluding them

45
Common Excel Functions (COUNT Example)
46
Inserting Rows (Best Practices)

• When creating formulas, include a blank row or
  column at the end of the formula range
• When adding a new row or column, the formula will
  be changed accordingly