Excel & Matlab

Assignment #1

The basic assignment is described first. Specific details follow so that all submissions are somewhat consistent. Generate x and y data for a straight line with random variation of the y variable. Plot the points on an Excel chart. Use the LINEST function to generate the statistics for the data points. Add a trendline to the graph. Compare the r^2, slope and intercept values obtained generated by the LINEST function and the reported values for the trendline. Record all of your actions using a Macro.

Specific instructions:

1. Start the Macro recorder

2. Insert the labels x, y1, y2 into cells A1, B1, and C1, respectively

3. In cells A2:A21 generate x values starting at 1 with increasing increments of 3

4. In cells B2:B21 generate the solution to the straight line equation y = mx + b for each of the x values in column A using m = 1.5 and b = 13

5. In cells C2:C21 add another set of y values which include a random error term added to the values in column B using the built-in RAND() function. The error should be within the range (25.0

6. Graph the points using column C for the y-axis and column A for the x-axis. Place the graph on the same sheet.

7. Return the results of the LINEST() function in cells A25:B29

8. Add a trendline to the chart which displays the r^2 value and the equation on the chart.

9. Compare the r^2, slope and intercept values form both methods and comment in cell A30

10. Rename Sheet 1 as Assignment1

11. Save the workbook as Assignment1

12. Turn the Macro recorder off (you will need the macro for Assignment #2).

Assignment #2

For this assignment you will perform the procedures identical to the first assignment but using Visual Basic code.

Specific instructions:

1. Copy Assignment1.xls (or .xlsx for Excel 2007) to a new file Assignment2.xls (or .xlsx as appropriate)

2. Use Excel to convert the Macro generated in Assignment1 into Visual Basic code.

3. Edit the Visual Basic classes and modules to insert extensive comments to clarify the operations that are being performed. Include blank lines as appropriate to make the code more readable.

4. Revise the program to calculate and populate the values in columns A-C

5. Include a procedure statement near the end of the code to rename Sheet Assignment1 to VB_Assignment2

6. Delete the Chart and erase all of the date in the Assignment1 sheet

7. Run the Visual Basic Code

8. Compare the results with those of Assignment1

9. In Cell A31 provide commentary on differences and problems encountered. Make sure the commentary does not require horizontal scrolling. You can enter this information directly into the cell without using programming in Visual Basic.

Assignment #3

Repeat Assignment #1 using Matlab. Specific details follow so that all submissions are somewhat consistent. Generate x and y data for a straight line with random variation of the y variable. Plot the points. Use the regress and stats functions to generate the statistics for the data points. Compare the r^2, slope and intercept values to those obtained in Excel.

Specific instructions:

1. Create a script file (.m) that contains all of your commands

2. Generate the data for x, y1, y2

3. Plot y2 vs x

4. Calculate r^2, slope and intercept

5. Calculate the predicted line based on the slope and intercept

6. Plot the predicted line

7. Include the numerical values on the graph

8. Compare the results with those obtained in Assignment #2