1 / 44

Akhila Kondai akhila.kondai@mail.wvu September 23, 2014

Day 11: MICROSOFT EXCEL – CHAPTER 7 CONTD . MICROSOFT EXCEL – CHAPTER 6 MICROSOFT EXCEL – CHAPTER 9 MICROSOFT EXCEL – CHAPTER 12. Akhila Kondai akhila.kondai@mail.wvu.edu September 23, 2014. Reminder. The Homework # 3 is due on September 26 th , 2014, Friday.

Download Presentation

Akhila Kondai akhila.kondai@mail.wvu September 23, 2014

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 11:MICROSOFT EXCEL – CHAPTER 7 CONTD.MICROSOFT EXCEL – CHAPTER 6MICROSOFT EXCEL – CHAPTER 9MICROSOFT EXCEL – CHAPTER 12 AkhilaKondai akhila.kondai@mail.wvu.edu September 23, 2014

  2. Reminder • The Homework # 3 is due on September 26th, 2014, Friday

  3. Microsoft Excel – Chapter 7 CONTD.

  4. Specialized functions • Math and Statistical Functions • Logical and Lookup Functions • Database Functions • Financial Functions

  5. Database functions • DSUM and DAVERAGE • DMAX and DMIN • DCOUNT

  6. DSUM AND DAVERAGE • DSUM function adds the values in a numeric database column based on conditions you specify in a range Syntax: DSUM(database, field, criteria) • DAVERAGE function determines the arithmetic mean, or average of numeric entries in database column that match conditions you specify Syntax: DAVERAGE(database, field, criteria)

  7. DMAX and dmin • DMAX function identifies the highest value in a database column that matches conditions you specify Syntax: DMAX(database, field, criteria) • DMIN function identifies the lowest value in a database column that matches conditions you specify Syntax: DMIN(database, field, criteria)

  8. DCOUNT • DCOUNT function counts the cells that contain numbers in a database column that match conditions you specify Syntax: DCOUNT(database, field, criteria)

  9. FINANCIAL functions • IPMT and PPMT • CUMIPMT and CUMPRINC • PV and FV

  10. Calculate interest and principal payments • IPMT function calculates periodic interest for a specified payment period on a loan or investment given a fixed interest rate, term and periodic payments Syntax: IPMT(rate,per,nper,pv,[fv],[type]) • PPMT function calculates the principal payment for a specified payment period on a loan or investment given a fixed interest rate, term, and periodic payments. Syntax: PPMT(rate,per,nper,pv,[fv],[type])

  11. Calculate cumulative interest and principal payments • CUMIPT function calculates cumulative interest for specified payment periods Syntax: CUMIPMT(rate,nper,pv,start_period, end_period) • CUMPRINC function calculates cumulative principal for specified payment periods Syntax:CUMPRINC(rate,nper,pv,start_period, end_period)

  12. Calculate present and future values • PV function calculates the present value of an investment Syntax: PV(rate,nper,pmt,[fv],[type]) • FV function calculates the future value of an investment Syntax: FV(rate,nper,pmt,[pv],[type])

  13. Microsoft Excel – Chapter 6

  14. What – if analysis • Data Table • Goal Seek • Scenario Manager

  15. WHAT-IF ANALYSIS • What if analysis is the process of changing variables to observe how changes effects calculated results. • A variable is an input value that can change to other values to affect the results of a situation.

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

  17. 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

  18. 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

  19. 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

  20. One-Variable Data Table

  21. 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

  22. Two-Variable Data Table

  23. Goal seek

  24. Create a Goal seek • Goal seek is a tool that identifies the necessary input value to obtain a desired goal. • What If Analysis ->Goal seek

  25. Scenario manager

  26. SCENARIO MANAGER • Scenario is a set of values that represent a possible situation. • Scenario manager enables you to define and manage scenarios to compare how they affect results.

  27. Using scenario manager • Create and edit Scenarios • View Scenarios • Generate Scenario Summary Report

  28. solver • Solver is an add-in application that manipulates variables based on constraints to find the optimal solution to a problem. • Solver is one of the most sophisticated what-if analysis tools, and people use Solver in a variety of situations and industries.

  29. Loading the solver add-in • Go to file tab • Select options  Add-ins • Manage->Excel Add-ins and click GO • Select Solver Add-in • You can see Solver option in Data ribbon

  30. Optimizing results with solver • Identify Objective cell, contains the formula-based value that you want to maximize, minimize. Or set to a value in Solver. • Identify Changing cells, contains a variable whose value changes until Solver optimizes the value in the objective cell

  31. Step 1: Define objective cell and changing variable cells. • Step 2 : Define constraints • Step 3 : Generate a report. • Click on SOLVE • Select ANSWER in the REPORT LIST • Observe the result in Answer Report 1 worksheet tab.

  32. Microsoft Excel – Chapter 9

  33. Multiple-sheet workbook management

  34. Grouping and ungrouping worksheets • All worksheets • Adjacent worksheets use SHIFT • Non Adjacent worksheets use CONTROL

  35. Worksheet reference • Pointer to a cell in another worksheet • Syntax : ‘Worksheet_name’!Range_Of_Cells • Select cell B1 in sheet 1 and calculate the average for cells J4:J20

  36. 3d formula • Formula or function that refers to the same range in multiple worksheets. • Select cell B2 in sheet 1 • Type =SUM( • Click sheet 2, press and hold shift and then click sheet 3 and select cell A1 • The formula becomes =SUM(Sheet2:Sheet3!A1)

  37. Inserting hyperlinks • Hyperlink is an electronic marker to another location in a worksheet, workbook, file, web page or email. • Select B1  Insert ribbon  Hyperlink in the Links group

  38. Microsoft Excel – Chapter 12

  39. Templates, themes and styles

  40. Selecting a template • Template is a special workbook file used as a model to create similarly structured workbooks • File -> New • Search for template • Select one • Preview of selected template

  41. Applying themes and backgrounds • A theme is a collection of colors, fonts, and special effects. • Page Layout ribbon • Themes • Select one • A background is an image that appears behind the worksheet data onscreen • Page Layout ribbon • Background -> select desired file

  42. Applying cell styles • A cell style is a set of formatting options applied to worksheet cells • Home ribbon • Styles -> Cell styles

  43. Questions ?

More Related