Problem Solving with Excel

1. Problem Solving with Excel Class Preparation

2. What Excel is… • Excel is a tool for… • storing, organizing, manipulating, and analyzing data • tabular and graphical representation • obtaining numeric solutions • It is an important problem solving tool

3. How does Excel relate to problem solving? Recall the five steps of the Problem Solving Method: Define Represent Plan Implement Evaluate Excel gives us many tools to use at each phase of the method after we have defined the problem. Let’s take a look at some of tools and functions Excel provides…

4. Tabular Format 2. Represent Histograms Tabular Format

5. Tabular Format 2. Represent (con’t) 3D Surfaces Flowcharts

6. 3. Plan • Sort function • Can be used to arrange data in ascending or descending order • It can help you organize data so you can use it more efficiently • Press F1 in Excel and search “sort” for more help • Filter function • Can be used to extract certain data of interest • Press F1 in Excel and search “filter” for more help Sort & Filter

7. 3. Plan (con’t) • Excel Hierarchy • Excel allows you to have a number of worksheets in the same workbook. • Like cell referencing within a worksheet, you can reference a cell in other worksheets of the same workbook • Use the following format – SheetName!CellAddress Excel Hierarchy

8. 3. Plan (con’t) Cell Referencing • A cell reference identifies the location of a cell in the spreadsheet • Acell reference consists of the column letter and row number that intersect at the cell's location • Cell references are used in formulas, functions, charts, and other Excel commands • These can be replaced by names which help identify their content.

9. 3. Plan (con’t) Cell Referencing You can give cells descriptive names to use in your formulas The Name Box displays the address of the selected cell. You can rename any cell for better readability.

10. 3. Plan (con’t) Cell Referencing Here, we renamed cell B2 as 'mass', because it stores the value for mass. We can also rename cell B3 as 'force'.

11. 3. Plan (con’t) Cell Referencing Now we can type in the equation referencing the cells by their new names, instead of their alphanumeric address.

12. 4. Implement Many Mathematical, Trigonometric and Statistical functions are available for finding numerical solutions.

13. Logical Functions • Logical Functions can be used to introduce decision making into your spreadsheet. There are 6 main logical functions: IF, AND, OR, NOT, FALSE, TRUE • The AND function returns TRUEif all arguments are true, and returns FALSE if one or more arguments are false. Let’s look at an example…

14. AND Function: Example Given Information: Cell A1 contains a value of 86, cell B1 contains a value of 89. The following AND function is typed in cell B3: = AND(A1>85,B1<88) After hitting the enter key, cell B3 will display: FALSE

15. IF Function • The IF function evaluates a statement and produces a result based on whether certain conditions are ‘true’ or ‘false’. • The user can specify different results to display for these conditions. • When the IF function evaluates a condition as true, the function will display the user’s desired ‘true’ result in the cell. • When the IF function evaluates a condition as false, the function will display the user’s desired ‘false’ result in the cell.

16. IF Function: Example 1 Given Information: Cell A2 contains a value of 105 The following IF function is typed in cell A3: =IF(A2<=100, "Within budget", "Over budget") After hitting the enter key, cell A3 will display: Over Budget

17. IF Function: Example 2 Given Information: Cell B2 contains a value of 98 The following IF function is typed in cell B3: = IF(B2<=100, "Within budget", "Over budget") After hitting the enter key, cell B3 will display: Within budget

18. Nested IF Function • To check for more than one condition a nested IF function can be used • Here, we use a nested IF function in the column of “Letter Grade” to assign a letter grade based on test score. The formula below is copied to all cells in this column. =IF(SCORE>90,"A",IF(SCORE>80,"B",IF(SCORE>70,"C")))

19. 5. Evaluate Conditional formatting helps interpret results Statistical functions

20. Conditional Formatting Logic • Conditional Formatting is a tool that allows you to apply formats to a cell or range of cells depending on the value of the cell or the value of a formula. • It can be used to change cell color, cell font, border setting to help users easily interpret results. • For example, you can have a cell appear in bold when its value is greater than 1000.

21. Conditional Formatting Example 50 Bolt diameters were measured • Conditional Formatting will be used to indicate values that are out of tolerance • Tolerances are the minimum and maximum allowable deviation from the ‘nominal’ or expected value • Use slide 22-28 to guide you through this example

22. Conditional Formatting Example Question: Which samples are out of tolerance? Hint: Note the nominal diameter and associated tolerance

23. Data with Conditional Formatting With conditional formatting, Excel highlights the results so they are easily seen.

24. How to use Conditional Formatting 1. Select the group of cells 2. Choose an appropriate rule under the “Conditional Formatting” Tool. [Conditional Formatting -> Highlight Cells Rules ->Greater Than…]

25. Step 1 – Greater Than Apply formatting to values greater than the nominal diameter plus tolerance.

26. Step 2 – Less Than This time identify values less than the minimum size. [Conditional Formatting -> Highlight Cells Rules ->Less Than…]

27. Step 2 – Less Than Apply formatting to values less than the nominal diameter minus tolerance

28. Final Result The data points highlighted in red are out of tolerance.

29. Summary • Excelis a tool for… • storing, organizing, manipulating, and analyzing data • tabular and graphical presentation • obtaining numerical solutions • It is an important problem solving tool