1 / 48

# Lecture 4 - PowerPoint PPT Presentation

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.

## PowerPoint Slideshow about ' Lecture 4' - perrin

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

• Importing Data

• Solver

• Macros

• 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

• 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

• From the Excel window select Save As Excel Workbook

• Some of Excel’s tools are not loaded automatically but must be activated by the user

• For example the Statistical tools called “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.

• 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?

• 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

• Format the traffic data, but save all work in a 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

• 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

• 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