Question

PLEASE LET ME KNOW HOW TO SOLVE THIS IN MICROSOFT EXCEL...EXCEL PLEASE

A company wants to study the relationship between an employee's length of employment and their

number of workdays absent. The company wants to be able to estimate the number of workdays absent

based on the employee's length of employment. The company collected the following information on a

random sample of seven employees. Somewhere in the spreadsheet: 1) Create a Scatter Chart to visually

show the relationship between the variables. Then calculate the slope and y-intercept. Finally, use the slope and y-intercept

in the simple linear regression equation to predict what the y value will be when x = 5, put you answer in cell B9.

X	Predicted Value for X = 5
5	?
Employee Selected for Sample	Length of Employment in Years (x)	Number of Workdays Absent (y)
Employee 1	5	2
Employee 2	6	3
Employee 3	9	3
Employee 4	4	5
Employee 5	2	7
Employee 6	2	8
Employee 7	0	8

Answer 1

1) Scatter Plot in Excel:

Select Data > Insert > Select the scatter Diagram( presents in the category of charts)

Scatter Diagram will display on screen. Select Scatter Diagram . Then go to Design > Add Chart Element > Select Axis title and Chart title.

Q 2) Regression

Go to Data tab >> Select Data Analysis > Select Regression - click Ok

Then Select Y and X range of data > Tick lables (first you select first row (heading row) of the data .

Select Output range (where you want results) > Click Ok

The regression Output is

The least Square value of slope and intercept are given in last table in regression output

The least square regression equation is

When X = 5,