 Download Download Presentation Day 10: Excel Chapter 7-8

# Day 10: Excel Chapter 7-8

Télécharger la présentation ## Day 10: Excel Chapter 7-8

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Day 10:Excel Chapter 7-8 Larry Reaveslarry.reaves@mail.wvu.edu September 18, 2013

2. Last class • Outlines/Groups • PivotTables • PivotCharts • Conditional Functions • Rank/Percentile

3. Nested if • IF functions can be nested • For example: • A store gives its employees commission based on sales • For sales <= \$1000, they earn 5% • For sales > \$1000 and <= \$5000, they earn 7.5% • For sales > \$5000, they earn 10% • IF(sales <= 1000, sales*0.05, IF(sales <= 5000, sales*0.075, sales*0.10))

4. Logical functions • Logical functions accept True and False values • AND(logical1, logical2) • Returns True if logical1 AND logical2 are both True, False otherwise • OR(logical1, logical2) • Returns True if either logical1 OR logical2 is True, False otherwise • NOT(logical) • Returns the opposite of logical. True -> False, False->True

5. Logical Function Example • For a class, bonus points are given for having no absences and also for having no late assignments • Show students who got bonus points in both categories • Show students who got bonus points for one category • Show students who got no bonus points

6. Match • MATCH(lookup_value, lookup_array, [match_type]) • Looks up the position of lookup_value in lookup_array • match_type: 1 finds the largest value <= lookup_value from an array in ascending order; -1 finds the smallest value >= lookup_value from an array in descending order; 0 finds an exact match for lookup_value

7. Index • INDEX(array, row_num, [column_num]) • Returns the value in array in the row specified by row_num • If the array range has multiple columns, you can specify which column you want to index by specifying the column_num argument

8. Match/INDEX Example • We have sales per city for a company • We would like to know • What are the lowest sales numbers for any city? • What is the city of lowest sales? • To calculate this we will also calculate • What is the position of the city with the lowest sales in our list?

9. Nesting functions graphically • Open the dialog for your outermost function • Click the argument box where the nested function is needed • Click the Name Box arrow to select the function you need • Enter the arguments for the nested function • Click the name of the outer function in the formula bar to return to the outer function’s dialog box

10. Advanced Filtering • Create a Criterion Range • Copy the labels from the table and add conditions for any fields you want to restrict • Multiple rows of conditions will create an OR filter • Data->Sort & Filter->Advanced • Filter in-place or Copy • Select data range • Select criterion range • Select location if copying

11. DSUM, DAVERAGE, DMAX, DMIN, DCOUNT • DSUM(data_range, field_name, criteria_range) • Same arguments for the other functions • data_range specifies the table we want to aggregate data from • field_name specifies the label above the column we would like to aggergate • criteria_range specifies the range with your criteria (like advanced filtering)

12. Financial Functions • IPMT(rate, per, nper, pv) • Calculates the interest for a specific payment period • PPMT(rate, per, nper, pv) • Calculates the principal repayment for a specific payment period • CUMIPMT(rate, nper, pv, start_period, end_period, type) • Calculates the cumulative interest paid • CUMPRINC(rate, nper, pv, start_period, end_period, type) • Calculates the cumulative principal repaid

13. PV and FV • PV(rate, nper, pmt) • Calculates the present value of future payments • FV(rate, nper, pmt) • Calculates the future value of an investment

14. What-if Analysis • What-If Analysis is a process of experimenting with different variables and assumptions to observe how they affect the results of the situation • We will see examples of one-variable analysis and two-variable analysis

15. One variable analysis • The variable you will be investigating will be tried for several different substitution values. • For example, changing the interest rate on a loan • Calculate the desired value for each possible substitution value

16. Series • Home->Editing->Fill->Series • Creates series of values you can use as substitution values • Step value: distance between values • Stop value: where you want the series to stop

17. What-IF Tool • Data->Data Tools->What-If Analysis->Data Table • Select the reference for the cell you want to replace • If your series is in rows, use ‘Row input cell’ • If your series in in columns, use ‘Column input cell’ • Click Ok to complete the table

18. Next Class • Two Variable What-If Analysis • Goal Seek • Scenario Manager • Solver • Hyperlinks • Linked Workbooks • Templates