Question

The file PO2_07.xlsx includes data on 32 employees at the (fictional) company Beta Technologies. Find the mean, median, and standard deviation of Annual Salary, broken down by each of the following categories. Round your answers to the nearest whole dollar, if necessary a. Gender Mean Gender Male Female $78187 $ 69135 $ 66,200 $ 69136 $ 35667 * $ 35667 Median Std Dev Comment on your findings. Females are getting paid less than males. b. A recoded version of Education, with new values 1 for Education less than 4, 2 for Education equal to 4, and 3 for Education greater than 4. Recorded Education less than 4 4 greater than 4 Mean Median Std Dev Comment on your findings. Salary increases as education increases. C. A recoded version of Age, with people aged less than 34 listed as Young, those aged at least 34 and less than 50 listed as Middle-aged, and those aged at least 50 listed as Older. Recorded Age Middle-aged Young Older Mean Median Std Dev

Answer 1

We have given data with variable Gender , Age , Prior Experience , Beta Experience , Education and Annual salary in $

Now we need to find Mean ,Median and Standard deviation of salary based on different categories

1) Based on Gender ( here given 0 , 1 for Female or Male )

2) Based on Education ( Less than 4 , 4 , greater than 4 )

3) Based on Age ( Young (age<34) , Middle age (34 $\small \leq$ Age < 50) , Older(Age $\small \geq$ 50) )

{ Print data for required labels only }

Employee	Gender	Age	Education	Annual Salary
1	1	39	4	63900
2	0	44	6	84000
3	0	24	4	45600
4	1	25	4	44200
5	0	56	8	156100
6	1	41	4	80300
7	1	33	6	50900
8	0	37	4	67100
9	1	51	6	137800
10	0	23	4	36600
11	0	31	6	61500
12	1	27	0	34800
13	0	47	4	67700
14	1	35	6	66200
15	1	29	0	28400
16	0	46	6	75900
17	1	50	4	106200
18	0	30	4	66200
19	1	34	4	58400
20	1	42	4	83900
21	1	51	8	134900
22	0	63	4	144600
23	0	28	4	55600
24	1	32	4	56500
25	0	55	6	143200
26	1	45	4	68700
27	0	34	2	34300
28	0	33	4	58600
29	1	23	4	48900
30	0	40	6	75800
31	1	48	4	88300
32	1	27	0	23000

For Q1

First we will find which observation represents male.

Male = 0

Sr no

Sr.No	Male Obs No.		Annual Salary
1		2	84000
2		3	45600
3		5	156100
4		8	67100
5		10	36600
6		11	61500
7		13	67700
8		16	75900
9		18	66200
10		22	144600
11		23	55600
12		25	143200
13		27	34300
14		28	58600
15		30	75800

We sort Salary of males in Ascending Order

[1] 34300 36600 45600 55600 58600 61500 66200 67100 67700 75800
[11] 75900 84000 143200 144600 156100

Thus Mean Salary for Male = $\small \sum \frac{xi}{n1}$ = (34300 + 36600 + .... +143200 +144600 + 156100) / 15

                                                = 1172800 / 15

                                                = 78186.67

        Mean Salary for Male   = 78187

Median Salary = ( n + 1 )/ 2 Obsⁿ = 8^th Obs            ( As n= 15 is odd number )

Median Salary = 67100

Standard deviation =

Var(xi)

Where $\small Var(xi)$ = =

(xi - Mean(x)) ni - 1

       = [ (34300 -78187 )² + ( 36600- 78187)² + .... +(143200-78187)² +(144600 -78187)² +

                                        (156100 -78187) ² ]/ (15-1)

   = 21018857333 / 14

$\small Var(xi)$ = 1501346952

Hence Standard deviation = =   38747.22

1501346952

Hence using similar formulas we will find mean , median and Standard deviation for every group

Mean (xi) = $\small \sum \frac{xi}{n}$

Median =( n + 1 )/ 2 Obsⁿ                          { if Observations (n ) are odd }

             = ( [n/2 ]+ [n/2+1] )/ 2         { if Observations (n ) are even }

Standard deviation =

Var(xi)

                                   Where $\small Var(xi)$ =

(x?- Meanıxl) n-1

Now we will find which observation represents Female.

Since it is calcualted

Mean Median and Std Dev using above formulas

For females

Mean =69135.29 = 69135

Median = 63900

Standard deviation = 33321.52

part 2)

Based on Education ( Less than 4 , 4 , greater than 4 )

i ) Less than 4 ( Education < 4 )

Sr No.	Less than 4 Obs No.	Salary
1	12	34800
2	15	28400
3	27	34300
4	32	23000

Sorted data

Xi = 23000 28400 34300 34800

Mean = $\small \sum \frac{xi}{n}$ = 30125     

Median = 31350    { here n = 4 }

Standard deviation = 5568.587

ii ) Equal to 4

Sr . no	4 Obs No.	Salary
1	1	63900
2	3	45600
3	4	44200
4	6	80300
5	8	67100
6	10	36600
7	13	67700
8	17	106200
9	18	66200
10	19	58400
11	20	83900
12	22	144600
13	23	55600
14	24	56500
15	26	68700
16	28	58600
17	29	48900
18	31	88300

Sorted data

Xi =

36600 44200 45600 48900 55600 56500 58400 58600 63900 66200
67100 67700 68700 80300 83900 88300 106200 144600

Mean = $\small \sum \frac{xi}{n}$ = 68961.11 = 68961   

Median = 65050    { here n = 18 }

Standard deviation = 25475.83

i ) Greater than 4 ( Education > 4 )

Sr No	Greater than 4 Obs No.	Salary
1	2	84000
2	5	156100
3	7	50900
4	9	137800
5	11	61500
6	14	66200
7	16	75900
8	21	134900
9	25	143200
10	30	75800

Sorted data

Xi = 50900 61500 66200 75800 75900 84000 134900 137800 143200 156100

Mean = $\small \sum \frac{xi}{n}$ = 98630   

Median = 79950    { here n = 10 }

Standard deviation = 39580.41

part 3)

Based on Age ( Young (age<34) , Middle age (34 $\small \leq$ Age < 50) , Older(Age $\small \geq$ 50) )

i) Young (age<34)

Sr No	age Obs No.	age	Salary
1	3	24	45600
2	4	25	44200
3	7	33	50900
4	10	23	36600
5	11	31	61500
6	12	27	34800
7	15	29	28400
8	18	30	66200
9	23	28	55600
10	24	32	56500
11	28	33	58600
12	29	23	48900
13	32	27	23000

Sorted Data

Xi - 23000 28400 34800 36600 44200 45600 48900 50900 55600 56500 58600 61500 66200

Mean = $\small \sum \frac{xi}{n}$ = 46984.62   

Median = 48900    { here n = 13 }

Standard deviation = 13182.18

ii) Middle age (34 $\small \leq$ Age < 50)

Sr No	age Obs No.	age	Annually Salary
1	1	39	63900
2	2	44	84000
3	6	41	80300
4	8	37	67100
5	13	47	67700
6	14	35	66200
7	16	46	75900
8	19	34	58400
9	20	42	83900
10	26	45	68700
11	27	34	34300
12	30	40	75800
13	31	48	88300

Sorted Data

Xi - 34300 58400 63900 66200 67100 67700 68700 75800 75900 80300 83900 84000 88300

Mean = $\small \sum \frac{xi}{n}$ = 70346.15   

Median = 68700   

Standard deviation = 14062.87

iii) Older ( Age $\small \geq$ 50)

Sr No	age Obs No.	age	Annually Salary
1	5	56	156100
2	9	51	137800
3	17	50	106200
4	21	51	134900
5	22	63	144600
6	25	55	143200

Sorted Data

Xi - 106200 134900 137800 143200 144600 156100

Mean = $\small \sum \frac{xi}{n}$ = 137133.3   

Median = 140500

Standard deviation = 16820.19