Lecture 4
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

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