# 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