Friends,
Ok... So I have a workbook with a list of around 200 different things, indexed from 1 to 200. I want excel to generate several random numbers between 1 to 200 to randomize which list items i choose... That part is easy...
BUT
I want to be able to add an "Importance Factor" so to speak, to certain list items to add to the likelyhood of their index number being selected.
Does anyone have a creative way of getting this done?
Thanks in advance,
-Mike
Last edited by michaeljoeyeager; 07-13-2012 at 02:35 PM.
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
...and with 15 posts you should know all that.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
You could break up the 200 list items into "importance factor" groups - e.g. group them accordingly to their assigned importance factor value (assuming you maybe have up to 10 separate importance factor values, if you have a lot - such as 150 importance factors, this method would be quite tedious). From there, get a random list number from each group, then use an importance factor modifier for each selected number, compare accordingly again, and then determine the winner. I think that should satisfy your request, but persons more familiar with true "random number generation" might correct me on that.
My mistake. I will start a new thread.
Read Richard's post & edit the Title of this one
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I read Richard's post carefully. It says, "If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title."
So I said I would repost. But I have edited the title of this thread.
My apologies again.
I think we need to know a little more.
For instance is there just one level of importance? i.e. Suppose the 'certain' list contains say 10 items, do they all have equal importance or is there a hierarchy.
Then assuming each of the 200 items has a random number between 1 & 200 assigned to it, it would be simple enough to pick say the top 20 by choosing the 20 highest random num bers.
However you're asking for something extra.
You appear to be saying that some numbers which may not be in the top 20 will carry an importance factor which, when added to their random value, will promote them above other non important numbers which are initially in the top 20 random numbers.
We can't determine what this factor should be since we don't know what thought processes you would want to apply to pick out these 'important' numbers. If you can describe what this 'rule' or process is we can perhaps help further.
There will definetely be a heirarchy.
Maybe it would be better to explain it like this...
In essence it would be like putting the numbers 1-200 in a hat. But an impotance factor integer determines how many times that number is in the hat. So every "normal" item has an IF of 1. Maybe the item with index # 30 has an importance factor of 15... So the number 30 is in the hat 15 times. And Maybe Index #165 has an IF of 50 so its number is in the hat 50 times. etc... Thus increasing their probability of being drawn.
So each item needs to be able to have the probability of it being chosen in a lottery adjusted...
What do you think?
Actually, I think what you suggested might be pretty spot on.
So I guess I could run a =RANDBETWEEN(1, 200) on each index number cell for each item with the intent of sorting and using the top, say, 7 integers. BUT before sorting add different values to those index cells based on their importance. So something REALLY important might have 50 added to it thus it will be very likely to be at the top of the list... etc.
That should work great... I was hoping for a bit more of a self-contained solution by somehow adjusting the =RANDBETWEEN function but this should work well enough... just a bit more leg work.
Yeah that's definitely more of a hack and slash solution michael but it should get the job done.
Yes those are the right lines
My thoughts are in the attached.
Columns A:D are the new randomised list each time. Once you've hit F9 to generate a new list I copied the A3:D22 and pasted them as values in F3:I22, then sorted F3:I22 by column I. Column F then gives you the weighted indexes.
Column N:O is the basic lookup table of importance.
It would make sense to encode this process in a macro.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks