lecture 4 n.
Skip this Video
Download Presentation
Lecture 4

Loading in 2 Seconds...

play fullscreen
1 / 48

Lecture 4 - PowerPoint PPT Presentation

  • Uploaded on

Lecture 4. Advanced Topics. OVERVIEW. Importing Data Data Analysis Add-In Solver Macros. IMPORTING DATA. You may receive data that is not in Excel that needs to be opened in Excel For example a text file, that is smaller and more amenable to email

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Lecture 4' - perrin

Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lecture 4

Lecture 4

Advanced Topics

  • Importing Data
  • Data Analysis Add-In
  • Solver
  • Macros
importing data
  • You may receive data that is not in Excel that needs to be opened in Excel
  • For example a text file, that is smaller and more amenable to email
  • Load this file into Excel, maintain the main columns
importing data from text file6
Importing Data From Text File
  • Save and Close the file
  • Open the file again from the folder
  • Notice the changes we made in the text file
  • The file is still a text fileeven though we have modifying it in Excel
importing data from text file7
Importing Data From Text File
  • From the Excel window select Save As Excel Workbook
data analysis add in
  • Some of Excel’s tools are not loaded automatically but must be activated by the user
  • For example the Statistical tools called “Data Analysis”
data analysis1
Data Analysis
  • Use Data Analysis to calculate Descriptive Statistics of the “Speed” column
  • Solver enables users to set up operations research problems e.g. integer linear programming, non linear programming etc
  • Operations research problems typically involve systems of equations that involve inequalities
  • Therefore, for non-inequalities problems we can use it as alternative, to say matrices, to solve systems of equations.
  • Solver must be activated from Add-Ins prior to use.
optimization using solver
Optimization Using Solver
  • Consider the following investment decision problem for a financial institution
  • Three new financial products yield returns of 6.5%, 7%, 9% respectively
  • Total investment amount in these products altogether cannot exceed $500K
  • Due to risk, product 3 investment cannot exceed twice product 2 amount and product 2 investment cannot exceed three times product 1
  • What is the optimal investment plan to maximize return on investment?
optimization using solver11
Optimization Using Solver
  • Note that the more objectives and constraints, the more complex the problem
  • Macros are used to record and save the keystrokes used to perform a task
  • By so doing the user can then call the macro to run the same tasks on another dataset
  • This is especially advantageous for repetitive type tasks, e.g. a weekly or daily traffic update report
  • Before we can use macros we must activate the Developer tab through Excel Options
activate developer tab
Activate Developer Tab
  • Format the traffic data, but save all work in a macro
record macro1
Record Macro
  • Keystrokes:
    • Highlight top row
    • Bold
    • All Borders
    • Fill color (green)
    • Select: Date, Time, Vehicle
    • Format
    • AutoFit Column Width
    • Scroll back to left
    • Highlight first 30 rows of speed column
    • Fill with orange color
    • Developer
    • Stop Recording
calling your macro
Calling Your Macro
  • Now open a new traffic data file
  • Copy and paste the data to the macros enabled file, making sure all columns and rows are lined up as in the macros enabled file
  • Run the macro from View Macro
call a macro
Call a Macro
  • The macro does the formatting on the new data for us in one shot
  • Note that we could have run it directly without copying over if we had selected the file name in the drop down
  • Students! Repeat the data import task, but this time save it as a macro
  • As we shall see macros can get VERY complex
  • http://office.microsoft.com/en-us/support/results.aspx?av=zxl
  • www.youtube.com