1 / 19

Advanced Excel Techniques: PivotTables, Functions, and What-If Analysis

This chapter focuses on advanced Excel features, including PivotTables, PivotCharts, and conditional functions. It teaches how to utilize nested IF functions for commission calculations, employs logical functions like AND/OR, and illustrates the use of MATCH and INDEX for data retrieval. Additionally, it covers advanced filtering techniques and financial functions such as IPMT and PPMT. The session concludes with an exploration of What-If Analysis, including one-variable and two-variable analyses to facilitate better decision-making in real-world scenarios.

mrinal
Télécharger la présentation

Advanced Excel Techniques: PivotTables, Functions, and What-If Analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related