1 / 9

Excel Data Tables

Excel Data Tables. DSC340 Mike Pangburn. Excel Data Table. T he single most powerful Excel feature this side of VBA for automating repetitive spreadsheet calculations. You want to be familiar with the Excel Data Table. Data Tables Excel is very powerful at making calculations.

thelma
Download Presentation

Excel Data Tables

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. Excel Data Tables DSC340 Mike Pangburn

  2. Excel Data Table The single most powerful Excel feature this side of VBA for automating repetitive spreadsheet calculations. You want to be familiar with the Excel Data Table

  3. DataTables Excelisverypowerfulat making calculations. LoanPayments: PMT(rate,nPeriods,pValue) Whatifwewanttoseehowourcalculationschangeaswechangethe parameters? Options: (i) We can manually adjust values and try to remember earlier results (ii) We can copy the formula across many cells and use different inputs for each copy (iii) We can use the best approach: the Excel Data Table Twodifferent types of DataTables: One-wayDataTables:Allows you tochangeoneparameter. Two-wayDataTables:Allows you tochangetwoparameters.

  4. One-WayDataTables Howtocreateone-waydatatables:: Creating a Data Table (example) • WehaveaparameterstoredinQ5 And,ourformula f()usestheparameter. • Wewanttoseehowtheresultchanges. whenweuseval1,val2,val3insteadofval. • Wefirstcreateatableset-up. • Then, chooseallthecellsintheset-uptable. • Goto:MenuData->Table(inoldExcel). • Goto:MenuData->What-ifAnaylsis->DataTable (inExcel2010). Equatethetopcelltothecellcontaining the formula. DataTableOptions RowInputCell?LeaveBlank ColumnInputCell?Choosethecellcontainingval(i.e.Q5) Writedownthevaluesthatweuseinstead ofval.

  5. Example: CarLoanPayments • Let’s create a 1-way Data Table: Car Loan Payments • Download Practice-advExcel-DataTables.xlsxfrom the course website • Weborrow$20000fromacreditortobuyacar. • Annualinterestrateis5%. • Weplantopaytheloanin36months. • OurmonthlypaymentscanbecalculatedbyPMTfunction: • MonthlyPayment=PMT(5/12,36,20000) • How do our payments change as theinterestratechanges?

  6. Two-WayDataTables ExcelDataTablesletsustoseetheimpactofchangeinparameters. Two-wayDataTables:Allowstochangetwoparameters. HowtocreateDataTable (example) Howtocreatetwo-waydatatables: • WehavetwoparametersstoredinR5andR6. And,ourformulausesbothoftheseparameters. • Wewanttoseehowtheresultchanges whenweuseval1,val2,val3insteadofval, andpara1,para2insteadofpara. • Wefirstcreateatableset-up. • Then, chooseallthecellsintheset-uptable. • Goto:MenuData->Table(inoldExcel). • Goto:MenuData->What-ifAnaylsis->DataTable. (in Excel 2010) Write down the values that we use instead of para. Equatethetop-leftcelltothe cellcontaining the formula. DataTableOptions RowInputCell?Choosethecellcontainingpar(i.e.R6) ColumnInputCell?Choosethecellcontainingval(i.e.R5) Writedownthevaluesthatweuseinstead ofval.

  7. Two-WayDataTables:Examples • Let’screatea couple two-waydatatables as examples: • MortgagePayments • MarketingProblem • Again we will use Practice-advExcel-DataTables.xlsxfrom the course website

  8. Example 2: MortgagePayments Weborrow$300000fromacreditortobuyahouse. Annualinterestrateis5%. Weplantopaytheloanin120months. OurmonthlypaymentscanbecalculatedbyPMTfunction: MonthlyPayment=PMT(5/12,120,300000) How do our payments change relative to changes to both the interestrateand/orpaymentperiod?

  9. Example 3: MarketingProblem Westartabusinesswithaninitialinvestmentof$20,000. Weincur$2toproduceagood. Weneedtosetapriceforourproduct,sayp. Wealsoneedtodecideonouradvertisingbudget,sayA. GivenpandA,totaldemandforourproductis: 2000+4 −20∗p WhichpandAmaximizeourprofit?

More Related