+ Reply to Thread
Results 1 to 24 of 24

Randomizing variables in lists

  1. #1
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Randomizing variables in lists

    I would like to create several lists filled with variables from x to y and then another cell to randomize all the lists upon clicking, how?

    Example:
    List one: numbers from 5-20
    List two: % from 20-80
    List three: one of 4 statements (ex: I like turtles / I like cats / I like dogs /I like snakes)

    Cell (or some way) to randomize the things in the lists:

    So when clicked, I would like to see something like this

    List one: 13
    List two: 64%
    List three: I like snakes

    How to do that, is it possible? Please help me

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Randomizing variables in lists

    Use the RANDBETWEEN function..

    See attached
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Thank you bro. But what should I do with it next. How do I make it generate the actual numbers?

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Randomizing variables in lists

    Assign this macro to a button..

    Please Login or Register  to view this content.
    When executed, it will refresh the list, and you'd need only the first row I reckon

  5. #5
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Thanks, how do I assign it to the button? I created the button and then clicked on assign macro and then edit and into the worksheet in visual basic put ActiveSheet.Calculate. It does not work. I know I am doing something wrong, I am new to this excel thing.

  6. #6
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    The error is Can't execute code in break mode, I have no idea how to resolve this as I am a newbie, could anybody please help me with it?

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Randomizing variables in lists

    hi there, welcome to the forum. is this for other users? you only have to press the F9 key to refresh/recalculate.

    if you really need a button, here's how. i assume from your statement you have already created a button? to go to the VBA editor, press ALT + F11. Insert -> Module. paste this inside:
    Please Login or Register  to view this content.
    right-click your button -> Assign Macro -> Refresh -> OK

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  8. #8
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Thank you. Got it now

  9. #9
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    One more thing. Lets say that a list number 4 is going to generate numbers dependent on list number three (List three: one of 4 statements (ex: I like turtles / I like cats / I like dogs /I like snakes)) in a way that
    When list number 3 generates sentences I like turtles and I like dogs a number from 30-90 is generated and when sentences I like cats and I like snakes are generated a number from 110-160 is generated

    How can I do that? Thanks for everything so far

  10. #10
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Comon help me with this last thing, I really need it.

  11. #11
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Randomizing variables in lists

    try..

    =IF(OR(C2="I like turtles",C2="I like dogs"),RANDBETWEEN(30,90),IF(OR(C2="I like cats",C2="I like snakes"),RANDBETWEEN(110,160),""))

  12. #12
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    It pops up an error "The formula you typed contains an error" , something is wrong

  13. #13
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Randomizing variables in lists

    Works for me..
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Hey, I probably just make a mistake, now it does not pop out an error but rather shows an empty cell
    Could you please look at the file, it is attached here (It is cell O6)

    PokerDrills.xls

    Thank you very much

  15. #15
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Guys could you please look at the file I posted to find out what am I doing wrong? Thank you very much.

  16. #16
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Randomizing variables in lists

    In M6 of your worksheet you kight want to you use this forumla and get rid of some unwanted spaces..

    =CHOOSE(RANDBETWEEN(1,4),"OOP vs 1 opponent","OOP vs 2+ opponents","IP vs 1 opponent","IP vs 2+ opponents")

  17. #17
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    This formula does not work, the formula I have in M6 works already, I dont quite get you

  18. #18
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Randomizing variables in lists

    The extra spaces in your existing formula in M6 wont allow the other dependent formula to give correct results. For instance you have "OOP vs 1 opponent " with an extra space at the end whereas in the formula in column O refers to just M6 being just "OOP vs 1 opponent".

    Also your existing formula starst with " "&choose(.... whereas your dependent formula dont have those spaces.

    Try the formula in my above post and let me know if you get desired results

  19. #19
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Yeh you are right I made a mistake there is an extra space. However after removing it I still dont get the results

  20. #20
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Hey please help me a lil with this issue, probably the last thing I need

  21. #21
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Guys please help me, I have already posted the file here, just tell me what am I doing wrong, I already deleted the space there but it still does not show any results

  22. #22
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Randomizing variables in lists

    the reason why i requested you upload a file is because Ace_XL, me & other members who attempted to solve your problem probably got it after removing the space. we can't tell why you can't. i've uploaded the file to show you, & all i did was to remove the space in M6

    1 possible reason is because your Auto-Calculation is off. another could be that you are using Excel 2003 as stated in your profile & you do not have the function RANDBETWEEN
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    11-26-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Randomizing variables in lists

    Well it did work for all cells except for that one. Anyways the file you posted it works, so thanks

  24. #24
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Randomizing variables in lists

    randbetween is available in 2003 when users install the analysis toolpak addin
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

+ 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