Lab #1 EXCEL (Office 365)

Lab #1 EXCEL (Office 365)

Simple procedures in Excel or other software programs can often times be overlooked because they are “so easy that everyone knows how to do them”.The reality is, however, that not everyone knows how to do these tasks, and they are often completed incorrectly.

The last page of this manual needs to be handed in as the report with answers to all posted questions .

1In this exercise we will be considering charting and data analysis.A chart can be created using menus.

To get help at any time press F1 function key or click “?” located in the top right hand site.

To create a graph:

Place data in two columns, i.e. X or independent data in Column B and the Y or dependant data in column C.

Fig. 1

We can now highlight the two columns of data, select insert tab on the ribbon and select the specific chart type.Select the properties you want the chart to have (we want an XY Scatter as the chart type).You should end up with the chart shown below. IF your cursor is not positioned inside the table with your numbers when you insert the chart, select the type you will end up with a blank chart area. Right click on the chart area, choose select data, then add, type Series name, select Series X values, then Series Y values. You graph will be generated.

Fig. 2

The data given to us was a line of the form Y = ……(enter your equation in the last page of the manual).

Find the best fit line and slope with intercept.

Select your chart area so that you can see a plus sign.

Click the plus sign on the right side of the chart to add chart elements.

Put the check mark on chart title (type y vs. x and your first and last name as your title), then label axis type y on y axis and x on x axis.

Next select the Trend line, select More trendline options by pressing on a small triangle beside the Trendline and select More Options. Select Format Trendline by clicking on the icon with bar graph.

In the dialog box that you will see, make sure that the checkbox for Display equation on the chart and the check box Display R-squared value on the chart are turned on. Explore different options for the type of your trendline. Select the best fit line based on the equation displayed. Report it as well as the R2 value in the answer sheet that you will hand in as your report.

Fig. 3

Now you will be using Analysis ToolPak to generate the trend line equation of the best fit and R2 value. You will compare the equation selected earlier with the slope and the intercept from the Regression Analysis output.

Analyze the data using a linear regression in order to find the slope and intercept.

The Analysis Tool Pack includes regression analysis tools. To access these tools, click Data Analysis in the Analysis group on the Data tab.

If the Data Analysis command is not available, see Appendix notes at the end of this manual to activate this Add-in feature of Excel.

Fig. 4

Now:

Select Data Analysis and scroll down until you see Regression.

Fig. 5

Select Regression and click on the data points on your chart.

Fig. 6

Input range for Y by highlighting your Y data, then for X highlight your X column and if not shown enter 95% Confidence Interval and press OK. For Output options leave New worksheet Ply selected.

Generate your output. Report the slope and intercept (x-variable shown in your regression table). Comment whether those values slope and intercept are the same as your values from best fit line equation generated in the previous step.

2Graph the following data. (You can copy and past the numbers directly from this word document into the Excel):

Variable A

Variable P

1

-12

2

-9

3

24

5

30

7

36

9

12

13

54

19

72

22

66

23

54

24

87

29

87

34

102

54

162

60

165

61

168

62

186

63

174

Show the equation of the best fit line and the R2 value in Excel.Do you consider the data to be linear? Support your answer on the last page of this manual?

3 IF function in Excel

Consider the following data, which you have been given and asked to analyze.

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,”Over 10″,”10 or less”) returns “Over 10” if A1 is greater than 10, and “10 or less” if A1 is less than or equal to 10.

IF(logical_test, value_if_true, [value_if_false])

Time hours

Population

0

3

1

3.852076

2

4.946164

3

6.351

4

8.154845

5

10.47103

6

13.44507

7

17.26381

8

22.16717

9

28.46321

10

36.54748

11

46.9279

12

60.25661

13

77.37102

14

99.34636

15

127.5632

16

163.7945

17

210.3162

18

270.0514

19

346.7529

20

445.2395

21

571.6988

Don't hesitate - Save time and Excel

Are you overwhelmed by an intense schedule and facing difficulties completing this assignment? We at GrandHomework know how to assist students in the most effective and cheap way possible. To be sure of this, place an order and enjoy the best grades that you deserve!

Post Homework
Top