+ Reply to Thread
Results 1 to 5 of 5

Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

  1. #1
    Registered User
    Join Date
    12-21-2008
    Location
    malaysia
    Posts
    14

    Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

    Hi, I desperately need help with something.

    I would like excel to randomly pick cells and insert an alphabet to a range of cells.

    For example.... 1 row of 20 and i would like excel to randomly pick 5 out of the 20 cells and insert an alphabet into it.

    someone posted this before

    Cell 1 =IF(RAND() <= 0.2, "a", "")
    Cell 2 Drag to cell 20 =IF(RAND() < (4 - COUNTIF(B$1:B1, "a") )/ (21 - ROW()), "a", "")

    Problem with this formula is that the excel sheet will auto generate when i click on other cells or when i start filtering.

    Appreciate it if someone is able to help

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

    To do anything like this and have numbers stay for more than one click at a time, you have to turn OFF automatic calculation on your sheet.

    Tools > Options > Calculation > Manual

    Just press F9 anytime you want the sheet to calculate.

    The down-side is this is a global setting, so the whole program turns manual. You'll have to remember to turn it back on when you're not working with this workbook any longer.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    12-21-2008
    Location
    malaysia
    Posts
    14

    Re: Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

    anyone care to explain in detail on what the formula actually means? as in the reason behind the action.
    =IF(RAND() <= 0.2, "a", "")
    =IF(RAND() < (4 - COUNTIF(B$1:B1, "a") )/ (21 - ROW()), "a", "")

    sometimes it generates 6 As instead of 5. Why is that?

    I'm a noob in excel. sorry for the trouble.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

    If you just type =RAND() into a cell you will get a long decimal random number. Press F9 and it will change. Over and over, each time your sheet registers a change of ANY kind, that formula will generate a new random number.

    You're longer versions are just ways of making the letter "A" appear or not based on the rand() number generated and a little extra math.

    According to those two formulas, the only values you should get are "blank" and "a"...not 6 or 5. You looking at a cell OTHER than one with those formulas?

  5. #5
    Registered User
    Join Date
    12-21-2008
    Location
    malaysia
    Posts
    14

    Re: Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

    Yes I get "A" only of course. But according to the formula, out of the 20 cells, there should only be 5 "A" instead of 6. Sometimes it generate six "A". after auto generating a few times

+ 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