Results 1 to 3 of 3

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. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    137

    Default 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
    =

    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.


    Many thanks in advance for your help

    C.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,362

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Aug 2002
    Posts
    137

    Default

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com