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

**Excript** · 03-12-2009, 02:07 AM

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

**JBeaucaire** · 03-12-2009, 03:10 AM

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.

**Excript** · 03-12-2009, 03:57 AM

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.

**JBeaucaire** · 03-12-2009, 04:43 AM

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?

**Excript** · 03-12-2009, 09:52 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions