Generating 5 ones or zeros in a row, in any sequence with 3-2 proportion

**szasz** · 04-20-2013, 07:39 PM

The title says the most of it.
1s and 0s, 5 of these in each row, in 5 different columns.

00111 or 01010 or 11001 or any other sequence, it doesn't matter.
The important part is the proportion.
3 0s with 2 1s or 3 1s with 2 0s

**martindwilson** · 04-20-2013, 08:41 PM

as far as i can see there are only 20 possible ways
00011
00101
00110
00111
01001
01010
01011
01100
01101
01110
10010
10001
10011
10100
10101
10110
11000
11001
11010
11100
how do you want to display this?

**szasz** · 04-20-2013, 08:55 PM

Sorry if it wasn't specific enough at first.
I want these generated randomly in five cells next to each other, in A1:E1 for example. So one digit in one cell.

and without using macros, if possible.

**martindwilson** · 04-20-2013, 09:10 PM

like this? hit f9 to recalculate

**szasz** · 04-20-2013, 09:34 PM

Almost!
I need this in many rows simultaneously, but I think I fixed that with extending the randbetween for each one.

do you have any idea why I can't use conditional formatting on the generated numbers ?

**lexusap** · 04-20-2013, 10:26 PM

Hi,

I hope this would help. I cant think of better way of doing it.

Go to sheet2, and paste special value column A to E and then filter it by 2 and 3, copy paste them to other sheet, then you will have random 3-2 proportion.

If you required more, then copy paste the formula on last row to 1000 or 1500..

Cheers
Lexusap

**martindwilson** · 04-21-2013, 04:58 AM

Almost! ????? that did exactly what you asked for.

I want these generated randomly in five cells next to each other, in A1:E1 for example

it's not exactly hard to remove the $ from $I$1 to $I1 then fill down col I as well
however regarding problem with conditional formating, don't forget MID() gives back a text number either use cell value equals ="1" or ="0"
or change the formula to =--MID(INDEX($J$1:$J$20,$I1),COLUMNS($A$1:A1),1) the the 1/0 will be real numbers
both ways on attached

**szasz** · 04-21-2013, 05:05 AM

Anyway Thank you Martin, I got what I wanted, the formatting might be some minor issue,

and lexusap, I used Martin's original file with some modification, but Thank you for your help too !

**szasz** · 04-21-2013, 05:06 AM

***repost***

**szasz** · 04-21-2013, 05:17 AM

Oh sorry I did not notice your reply Martin!

Yes, you did exactly what I asked! I was just hoping I could simply expand it and it will genetrate random different sequences not the same one multiple times, but as I said I fixed that with creating a randbetween for each row.
The formatting problem is solved as well.

Once again, Thank you for your help !