You are here:

Excel/rand? generate 1-4 with a twist...

Advertisement

Expert: Nathan Head - 10/22/2009


Question

Randomizer
Hello,

I am so hoping for some help here, I'm a beginner who has been trying to self teach excel (because courses are so expensive)I have spent the better part of 9 hrs trying different versions of rand without a clue how to move 1 of the random alocated numbers to 5 particular staff eg:

Objective is to allocate the same number of staff to each supervisor, 1 of the 4 supervisors already has  5 staff as per M column and any overflow of unequal staff numbers should also go to her.

Need to generate a random number between 1 and 4 for Supervisors in C column (Only 1 of each number) the number given to A5 should then be given to "Carrie's Staff" in 5 and displayed in colunn O

Generate a random number between 1 and 4 for Acting Sups  in F column (Only 1 of each number)

Generate a random number between 1 and 4 for Partimers in L column (Only 1 of each number)

Generate random numbers in equal amounts in column I BUT the overflow/extra 2 people should also be given to the number generated to A5

I dont even know if this is possible... your thoughts or assistance on this would be magical..

Thank you so much in advance for any attention, I am a huge fan of this site and the people who donate their time.

Isabella

Answer
Isabella:

I have uploaded a sample spreadsheet here:
http://home.swbell.net/nate-sus/excel/randomizer.xls

I must point out I'm not 100% certain I understood the question; however, I took a stab at what I thought you were trying to do.  Please email me at Lotus@swbell.net if I misunderstood and I can likely tweak further.

All the cells in yellow are helper formulas that I needed to perform some extra calculations for the randomizer.

My ultimate goal was for the randomizer to assign exactly 5 employees to A, B, and D.  It assigns Carrie her employees and the 1 extra from the Fultimers (you mentioned 2 extra people but I only had 1 extra).  No matter how many times you refresh the randomizer will always have assigned 5 random people to A, B, and D.  It will always assign 6 random people to Carrie plus her specified 5 employees for a total of 11.

I hope this is what you were looking for!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Nathan Head

Expertise

Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't have any expertise there).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released. As a CPA, I use spreadsheets every day at work.

Education/Credentials
CPA, Texas

©2009 About.com, a part of The New York Times Company. All rights reserved.