# Creating an Indicator Variable

This is a discussion on Creating an Indicator Variable within the Excel Questions forums, part of the Question Forums category; Dear friends, I have the following table: Column A includes the company name, Column B the country name in which ...

1. ## Creating an Indicator Variable

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

******** ******************** ************************************************************************>
 Microsoft Excel - q.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1 =

A
B
C
D
E
F
1
company namecountryindustry#employees indicator variable
2
AUK200355 1
3
BUS2003111 1
4
CUK200319 0
5
DUK200350 etc
6
EUS200377
7
AUK200457
8
BUS2004172
9
CUK200418
10
DUK200450
11
EUS200479
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

C.

2. F2:

Control+shift+enter...

=(D2>AVERAGE(IF(\$B\$2:\$B\$11=B2,IF(\$C\$2:\$C\$11=\$C\$2,\$D\$2:\$D\$11))))+0

and copy down.

I really appreciate that!!!

We are all grateful for having you making such an important contribution to the forum

C.florackis

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•