Creating an Indicator Variable

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
q.xls
ABCDEF
1company namecountryindustry#employeesindicator variable
2AUK2003551
3BUS20031111
4CUK2003190
5DUK200350etc
6EUS200377
7AUK200457
8BUS2004172
9CUK200418
10DUK200450
11EUS200479
Sheet1


Many thanks in advance for your help

C.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
Hi Aladin,

once more your advice has been fantastic

I really appreciate that!!!

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

C.florackis
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top