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

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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