none
Generate Equally distributed Random numbers between a range in Excel RRS feed

  • Question

  • Hi All,

    I am trying to generate random numbers between a range  (1 to 4) of offices to be equally distributed against 60 individuals i.e. each individual should map to a number (office) between the range and each office should have only 15 individuals to work with.

    I am trying to find this but am not able to map exactly 15 individuals to each office. Your help is highly appreciated.

    TIA.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Friday, June 1, 2012 10:09 AM

Answers

  • Let's say the employees are in A2:A61.

    Enter 1 in B2:B16, 2 in B17:B31, 3 in B32:B46 and 4 in B47:B61.

    Enter the formula =RAND() in C2:C61.

    Sort B2:C61 on column C. Make sure NOT to include A2:A61 in the sort!

    You now have random assignments in B2:B61.


    Regards, Hans Vogelaar

    Friday, June 1, 2012 10:59 AM

All replies

  • Let's say the employees are in A2:A61.

    Enter 1 in B2:B16, 2 in B17:B31, 3 in B32:B46 and 4 in B47:B61.

    Enter the formula =RAND() in C2:C61.

    Sort B2:C61 on column C. Make sure NOT to include A2:A61 in the sort!

    You now have random assignments in B2:B61.


    Regards, Hans Vogelaar

    Friday, June 1, 2012 10:59 AM
  • Wow Hans.... this is an elegant solution, so simple but solves the problem in hand.

    Thanks a lot :)


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Friday, June 1, 2012 12:26 PM