abc weighted random

[Kishan]

Using Excel 2000

Hi,

I want a "1" random cell to be filled en the each row C6:E12 imagine there are 7
Lottery winning drum...

In the first Lottery winning drum there are 48 balls with letter a, 31 balls with letter b, 21 balls with letter c total 100 in the C6:E6.
So it is possible the random take in account the weight of filled balls a, b, c and fill the cell accordingly or it has to be normal random selections

And in the rest Lottery winning drum C7:E12 will be applied the same method.

So far 7 random selected cells will be highlighted in the range C6:E12

Balls With	Balls With	Balls With
Letter a	Letter b	Letter c
48	31	21
71	18	11
40	30	30
56	26	18
74	16	10
27	34	39
34	33	33

Please I need some "Formula or VBA" solution for my this problem

Thank you in advance

Regards,
Kishan

 

[J.Ty.]

HI,

Is the following what you need?

Book5
	A	B	C	D
1	Balls With	Balls With	Balls With	Random choice
2	Letter a	Letter b	Letter c
3	48	31	21	a
4	71	18	11	a
5	40	30	30	c
6	56	26	18	a
7	74	16	10	a
8	27	34	39	b
9	34	33	33	a
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=MID(REPT("a",A3)&REPT("b",B3)&REPT("c",C3),RANDBETWEEN(1,SUM(A3:C3)),1)

 

[Kishan]

J.Ty., yes that is what I wanted, but it is giving a result in the column "D", instead is it possible to highlight the 1 cell in each row and total 7 in the range A3:C9?

Thank you for help

Kind Regards
Kishan

 

[J.Ty.]

The second attempt still uses column D, but you can hide it or transfer the cells to some other location.

Book5
	A	B	C	D
1	Balls With	Balls With	Balls With	Random choice
2	Letter a	Letter b	Letter c
3	48	31	21	a
4	71	18	11	a
5	40	30	30	b
6	56	26	18	a
7	74	16	10	a
8	27	34	39	a
9	34	33	33	c
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=MID(REPT("a",A3)&REPT("b",B3)&REPT("c",C3),RANDBETWEEN(1,SUM(A3:C3)),1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:C9	Expression	=RIGHT(A$2;1)=$D3	text	NO

 

[Kishan]

J.Ty., yes your conditional formatting did a trick, as I am using older version I changed the row 2 with only a, b and c, than applied the formula =SI($D3=A$2;1;0) and all worked fine.

Thank you very much for your help

Kind Regards
Kishan

 

[J.Ty.]

Thanks for the feedback and good luck!

 

[J.Ty.]

BTW, if you want to help us help you, please edit your profile and indicate there the Excel version you are using. It is in the "Account details" section.

 

[Fluff]

@J.Ty.
There isn't an option for Xl 2000, the earliest version in the list 2007.
Also Kishan always puts his Xl version in his op

 

[J.Ty.]

@J.Ty.
There isn't an option for Xl 2000, the earliest version in the list 2007.
Also Kishan always puts his Xl version in his op

Indeed, I have overlooked it. Sorry Kishan.

 

[Kishan]

@J.Ty.
There isn't an option for Xl 2000, the earliest version in the list 2007.
Also Kishan always puts his Xl version in his op

Thank you Fluff, for your reply to J.Ty. much kind of you

Kind Regards
Kishan