+ Reply to Thread
Results 1 to 3 of 3

ranking numbers in different groups

  1. #1
    Registered User
    Join Date
    04-26-2011
    Location
    Rotterdam
    MS-Off Ver
    Excel 2007
    Posts
    12

    ranking numbers in different groups

    hi all,

    I am trying to categorize two digit sic-codes (industry of a company) into their major industry group.

    the numbers for my two digit sic codes range from 0-99.

    I want to categorize them in the following groups
    Sic codes from
    1-9 = 1
    10-14 = 2
    15-17 =3 etc...
    20-39
    40-49
    50-51
    52-59
    60-67
    70-89
    90-97
    99


    i tried the if-function like this:
    =if(E2<10;1;if(9<E2<15;2;if(14<E2<18;3;if(19<E2<40;4;if(39<E2<50;5;if(49<E2<52;6;IF(51<E2<60;7;if(59<E2<68;8;if(69<E2<90;9;if(90<E2<98;10;if(E2=99;11;"Kan niet")))))))))))

    But unfortunately it does not work. Can anyone help me with this issue? Thanks!
    Last edited by oseroser; 11-01-2012 at 03:52 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: ranking numbers in different groups

    =LOOKUP(E2, {0,10, 15,18,20,40,50,52,60,70,90,99}, {1,2,3,4,5,6,7,8,9,10, 11, 12})
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    04-26-2011
    Location
    Rotterdam
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: ranking numbers in different groups

    You're a hero! thank you very much!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1