Question

Application assignment: using Excel

Descriptive Statistics of Quantitative Data (measured on Ratio scale).

Use this file for submission, but use Excel to do your work and copy and paste in the appropriate places identified in this word file.

Note: The following Excel functions in answering Questions 1 and 2 below.

AVERAGE , AVEDEV, COUNT, CORREL, MIN, MAX, MDIAN, MODE, STDEV , STDEVP, STDEV.P, and STDEV.S

Q3. Data Analysis descriptive   Option on Excel 2010 or later to obtain descriptive statistics (Mean, Standard Error, Median, Mode,… ) of both variables X and Y.

Insert your answer here .

Note: Copy the data for the two variables into an excel file and calculate the Descriptive Statistics of both X and Y (using Excel) as explained in class. After you find the required statistics in excel copy them and paste your answer below.

Descriptive for the X values:

Descriptive for the X values:  

Table 1

ID	X	Y
1	3	29
2	4	32
3	4	34
4	5	36
5	5	37
6	5	38
7	5	39
8	6	40
9	6	41
10	6	42
11	6	42
12	7	43
13	7	44
14	7	44
15	7	45
16	7	45
17	8	46
18	8	46
19	8	47
20	8	47
21	9	48
22	9	48
23	9	49
24	9	49
25	9	50
26	10	51
27	10	51
28	10	52
29	10	52
30	10	53
31	11	53
32	11	54
33	11	54
34	11	55
35	12	55
36	12	56
37	12	56
38	13	57
39	13	58
40	13	58
41	14	59
42	14	60
43	15	61
44	15	62
45	16	63
46	17	64
47	18	66
48	19	68
49	21	71
50	23	73

Answer 1

Excel file is pasted below.

The formulas used are given below.

In Cell B52 Type =AVERAGE(B2:B51)

In Cell C52 Type =AVERAGE(C2:C51)

In Cell B53 Type =AVEDEV(B2:B51)

In Cell C53 Type =AVEDEV(C2:C51)

In Cell B54 Type =COUNT(B2:B51)

In Cell C54 Type =COUNT(C2:C51)

In Cell B55 Type  =CORREL(B2:B51,C2:C51)

In Cell B56 Type =MIN(B2:B51)

In Cell C56 Type =MIN(C2:C51)

In Cell B57 Type =MAX(B2:B51)

In Cell C57 Type =MAX(C2:C51)

In Cell B58 Type =MEDIAN(B2:B51)

In Cell C58 Type =MEDIAN(C2:C51)

In Cell B59 Type =MODE(B2:B51)

In Cell C59 Type =MODE(C2:C51)

In Cell B60 Type =STDEV(B2:B51)

In Cell C60 Type =STDEV(C2:C51)

In Cell B61 Type =STDEVP(B2:B51)

In Cell C61 Type =STDEVP(C2:C51)

In Cell B62 Type =STDEV.P(B2:B51)

In Cell C62 Type =STDEV.P(C2:C51)

In Cell B63 Type =STDEV.S(B2:B51)

In Cell C63 Type =STDEV.S(C2:C51)