- 69 Views
- Uploaded on

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

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
- Load this file into Excel, maintain the main columns

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 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 Analysis

- Use Data Analysis to calculate Descriptive Statistics of the “Speed” column

SOLVER

- 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

- 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 Solver

- Note that the more objectives and constraints, the more complex the problem

MACROS

- 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

- Format the traffic data, but save all work in a macro

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

- 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

- 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

References

- http://office.microsoft.com/en-us/support/results.aspx?av=zxl
- www.youtube.com

Download Presentation

Connecting to Server..