cflorackis
Board Regular
- Joined
- Aug 18, 2002
- Messages
- 137
Dear friends,
I have the following table: Column A includes the company name, Column B the country name in which the company operates, Column C the corresponding financial year and Column D the number of employees in each company.
I need to create an indicator variable (in Column F) that takes the value of 1 if the number of employees in the company is larger than the average number of employees in companies that operate in the same country for each year, and zero otherwise.
For example, the average number of employees in UK companies for year 2003 is 41.33333 [(55+19+50)/3] while the average number of employees in US companies in 2003 is 94 [(111+77)/2]. Then, the entry in F2 for my indicator variable should be 1 since 55>41.33333. The entry in F3 should be 1 since 111 > 94. The entry in F4 should be 0 since 19<41.33, and so on
Is it possible to get column F using an excel function rather than doing it manually. Here is my table
Many thanks in advance for your help
C.
I have the following table: Column A includes the company name, Column B the country name in which the company operates, Column C the corresponding financial year and Column D the number of employees in each company.
I need to create an indicator variable (in Column F) that takes the value of 1 if the number of employees in the company is larger than the average number of employees in companies that operate in the same country for each year, and zero otherwise.
For example, the average number of employees in UK companies for year 2003 is 41.33333 [(55+19+50)/3] while the average number of employees in US companies in 2003 is 94 [(111+77)/2]. Then, the entry in F2 for my indicator variable should be 1 since 55>41.33333. The entry in F3 should be 1 since 111 > 94. The entry in F4 should be 0 since 19<41.33, and so on
Is it possible to get column F using an excel function rather than doing it manually. Here is my table
q.xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | company name | country | industry | #employees | indicator variable | |||
2 | A | UK | 2003 | 55 | 1 | |||
3 | B | US | 2003 | 111 | 1 | |||
4 | C | UK | 2003 | 19 | 0 | |||
5 | D | UK | 2003 | 50 | etc | |||
6 | E | US | 2003 | 77 | ||||
7 | A | UK | 2004 | 57 | ||||
8 | B | US | 2004 | 172 | ||||
9 | C | UK | 2004 | 18 | ||||
10 | D | UK | 2004 | 50 | ||||
11 | E | US | 2004 | 79 | ||||
Sheet1 |
Many thanks in advance for your help
C.