Day 9 – Excel chapter 6, 7, 8

1. Day 9 – Excel chapter 6, 7, 8 Divya Reddy Ravipalli diravipalli@mix.wvu.edu September 15th, 2014

2. announcements Upcoming Significant Dates: • 9.15.2014 – MyITLab Lesson B Due • 9.19.2014 – Homework #2 Due • 9.26.2014 – Homework #3 Due • 9.29.2014 – Exam#1(Section 12) • 10.1.2014 – Exam#1(Section 14)

3. Objectives • Conditional math • Statistical formulas • Nested logical functions • Data analysis tool • Goal seek

4. Math and statistical functions

5. SUMIF • SUMIF function adds the cells specified by a given condition • Called as Conditional Sum because it sums the values that meet a particular condition. • Syntax =SUMIF( Range, Criteria, [Sum_range])

6. Example • Open Conditional Math.xlsx • In cell C18,use SUMIF • We need to find value of convertibles • C4:C15 is the range for models, ‘Convertible’ is the criteria and H4:H15 has the values for the cars (Sum range) • So the syntax is =SUMIF(C4:C15,"Convertible", H4:H15)

7. AVERAGEIF • The AVERAGEIF function is used to average a set of values based on a certain criteria. • Syntax =AVERAGEIF( Range, Criteria, [Average_range]) • In cell C20 use AVERAGEIF • =AVERAGEIF(C4:C15, “Hardtop” ,Acquired)

8. COUNTIF • The COUNTIF function is used to count how many times a certain value occurs. • Syntax =COUNTIF( Range, Criteria) • In cell C22 use COUNTIF • =COUNTIF(C4:C15, “Hardtop”)

9. LOGICAL FUNCTIONS – ‘AND’ ‘OR’ The AND function evaluates to true only if everything individually evaluates to true. • The OR function evaluates to true if at least one thing individually evaluates to true. AND OR

10. NESTED FUNCTION – IF with AND • In the next example, salespeople who have worked for XYZ security systems more than seven years AND have annual sales more than $32,000 will be assigned a job classification of 4. All Others have job classification code of 2 • In cell E5 • Type =IF((AND(D5>7,C5>32000)),4,2) • Copy formula through E12

11. Nested Function – IF with OR • In cell F5 type • =IF((OR(D5>7,C5>32000)),4,2) • Salespeople who worked for more than 7yrs OR have annual sales of >32,000 • Copy formula through cell F12

12. Nested IF • Salespeople who have worked for XYZ Security Systems more than seven years AND have annual sales of more than $32,000 will be assigned a job classification of 4. • An employee meeting either criterion receives a job classification of 2, and an employee meeting NO criteria is assigned a job classification code of 1 • In Cell G5 type • =IF((AND(D5>7,C5>32000)),4,(IF((OR(D5>7,C5>32000)),2,1)))

13. What-if analysis

14. What-if analysis • What if analysis is the process of changing variables to observe how changes effects calculated results. • A variable is a value that you can change to see how that change effects the other value.

15. Data table • One variable Data Table • Two variable Data Table

16. one-variable data table • A one-variable data table ─ a data analysis tool that provides various results based on changing one variable • A substitution value ─ replaces the original value of a variable in a data table

17. Create a One-Variable Data Table • List substitution values in the left column or first row • Enter formulas in the first row or left column (whichever was not used above) • Create the one-variable data table • Format the results of the data table • Create custom number formats to disguise the formulas as headings

18. One-Variable Data Table To complete a one-variable data table: • Select entire table starting in the blank cell in the top-left corner • Click What-If Analysis in the Data Tools group on the Data tab and select Data Table • Enter address of the cell to be changed in the Data Table dialog box • Click OK

19. One-Variable Data Table

20. Two-Variable Data Table • A two-variable data table ─ a data analysis tool that provides results based on changing two variables • Creating a two-variable data table ─ similar to creating a one variable data table; however, you are limited to comparing one result. • Recommendations include: • Use the top row for one variable’s substitution values • Use the first column for the other variable’s values • Apply a custom number format to the formula cell in the top-left cell

21. Two-Variable Data Table

22. Goal seek

23. Goal Seek • Goal Seek ─ a tool when you know the desired end result but not the value needed to meet the goal • Enables you to work backwards to solve a problem • Excel can enter the input value in the variable cell

24. To use Goal Seek • Click What-If Analysis in the Data Tools group on the Data tab • Select Goal Seek to open the Goal Seek dialog box • Enter the cell reference for the cell to be optimized in the Set cell box • Enter the result you want to achieve (such as $300) in the To value box • Enter the cell reference that contains the value of the variable to adjust (such as cost of car) in the By changing cell box • Click OK • When an answer appears, click OK to accept the change or Cancel to return to the original data

25. Use Goal Seek To improve the safety of state residents and slow the rapid growth of incarcerated people, the State of West Virginia is determined to stabilize crime rates. • a. Use the Goal Seek tool to find a rate of change that results in a violent crime rate of 271 (the historical average from 1960-2011) for the year 2020 in cell B15. Have Goal Seek adjust the value of cell B3 until it locates the correct value.

27. b. Use the Goal Seek tool to find a rate of change that results in a property crime rate of 2,500 for the year 2020 in cell C15. Have Goal Seek adjust the value of cell C3 until it locates the correct value.

29. Next class • Scenario Manager • Solver