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):

Show the equation of the best fit line and the R² 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])

The population represents the number of active bacterium present in a sample at a given time.The process and company are most profitable when less than 500 of bacteria are present.

Using IF function generate answers in the third column. Apply “IF” function to show at what time we need to take action (population >= 500).

4. Using Solver

Enter the following data into your Excel file (copy and paste):

The population represents the number of active bacterium present in a sample at a given time (hours).When the bacteria population in the sample reaches 4000, there is a need for action.At what time will you need to take action?The process and company are most profitable when the maximum (less than 4000) number of bacteria are present.

For this example you need to use Solver which is a part of What-if analysis. If it is not loaded into Excel you must go to Excel / Options / Add-ins / Excel Add-ins, select Analysis Toolpak and Solver (see Appendix).

Follow the steps:

copy and paste data from this manual into the worksheet,

generate graph (XY scatter plot)

get the best fit line with R² . In your case this will not be a straight line. The R² should be equal to one.