If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Excel questions (bingo 1-60 numbers)

Thread starter Legacy 510990
Start date Oct 12, 2023

L

Legacy 510990

Guest

Oct 12, 2023

#1

Sorry to bother you. I really need help
I want to make an Excel bingo file from 1-60 numbers. includes 6 columns and 15 rows
column 1: from 1 to 9
Column 2: from 10 to 19
Column 3: from 20 to 29
Column 4: from 30 to 39
Column 5: from 40 to 49
Column 6 : from 50 to 60
How can I get different values every time I press F9?

Excel Facts

What does custom number format of ;;; mean?

Click here to reveal answer

Three semi-colons will hide the value in the cell. Although most people use white font instead.

Sort by date Sort by votes

6StringJazzer

Well-known Member

Oct 12, 2023

#2

You can generate a random number from 1-60 each time you press F9 by using this formula

Excel Formula:

=RANDBETWEEN(1,60)

Other than that, I have no idea of what you are trying to do. The picture you attached isn't helpful without more of a description. (This looks nothing like Bingo to me, which has 5 columns and 5 rows)

Upvote 0

L

Legacy 510990

Guest

Oct 12, 2023

#3

6StringJazzer said:

Bạn có thể tạo một số ngẫu nhiên từ 1-60 mỗi lần nhấn F9 bằng cách sử dụng công thức này

Excel Formula:

=RANDBETWEEN(1,60)
[/MÃ SỐ]

Ngoài ra, tôi không biết bạn đang cố gắng làm gì. Hình ảnh bạn đính kèm sẽ không hữu ích nếu không có thêm mô tả. (Đối với tôi, cái này trông không giống Bingo, nó có 5 cột và 5 hàng)
[/QUOTE]
Cảm ơn rất nhiều. 
Mình  muốn thay đổi vị trí ngẫu nhiên và số ngẫu nhiên đồng mãn 2 điều kiện: 
[COLOR=rgb(226, 80, 65)][B]* Điều kiện 1:[/B][/COLOR] 
Cột 1: từ 1 đến 9 
Cột 2: từ 10 đến 19 
Cột 3: từ 20 đến 29 
Cột 4: từ 30 đến 39
 Cột 5: từ 40 đến 49 
Cột 6: từ 50 đến 60 
[COLOR=rgb(226, 80, 65)][B]* Điều kiện 2[/B][/COLOR][B] :[/B] 
Hàng ngang có 4 số

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Oct 12, 2023

#4

@inveloto
Please post in English only in this forum per the forum's outline

If you want to ask in another language, then please use the Questions in Other Languages forum

Also, do not write your response within a quote of a previous post

Upvote 0

L

Legacy 510990

Guest

Oct 13, 2023

#5

Thanks a lot.
I want to change random positions and random numbers that satisfy 2 conditions:
Condition 1:
Column 1: from 1 to 9
Column 2: from 10 to 19
Column 3: from 20 to 29
Column 4: from 30 to 39
Column 5: from 40 to 49
Column 6: from 50 to 60
* Condition 2:
The horizontal row has 4 numbers

Upvote 0

A

Anonymous1378

Board Regular

Oct 13, 2023

#6

If you don't mind lots of helper columns (doubt it can be avoided too much with your excel version anyway?), a simplistic way would be as follows

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

1

10

20

30

40

50

2

2

11

21

31

41

51

3

3

12

22

32

42

52

4

4

13

23

33

43

53

5

5

14

24

34

44

54

6

6

15

25

35

45

55

7

7

16

26

36

46

56

8

8

17

27

37

47

57

9

9

18

28

38

48

58

10

19

29

39

49

59

11

12

13

14

15

Sheet1

Cell Formulas
Range	Formula
H1:M15	H1	=RAND()
O1:T15	O1	=INDEX(A$1:A$15,RANK.EQ(H1,H$1:H$15))&""

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Oct 13, 2023

#7

@Anonymous1378
That suggestion does not seem to meet this given criteria.

inveloto said:
* Condition 2:
The horizontal row has 4 numbers

Upvote 0

A

Anonymous1378

Board Regular

Oct 13, 2023

#8

Yeah I'm not entirely sure how I would get around that without a fixed combination of row types, which are then re-ordered at random.

Book1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1

1

10

20

30

40

50

1

1

1

1

2

2

11

21

31

41

51

1

1

1

1

3

3

12

22

32

42

52

1

1

1

1

4

4

13

23

33

43

53

1

1

1

1

5

5

14

24

34

44

54

1

1

1

1

6

6

15

25

35

45

55

1

1

1

1

7

7

16

26

36

46

56

1

1

1

1

8

8

17

27

37

47

57

1

1

1

1

9

9

18

28

38

48

58

1

1

1

1

10

19

29

39

49

59

1

1

1

1

11

60

1

1

1

1

12

1

1

1

1

13

1

1

1

1

14

1

1

1

1

15

1

1

1

1

16

17

18

19

20

21

22

23

24

25

26

27

Sheet1

Cell Formulas
Range	Formula
P1:U15	P1	=INDEX(H$1:H$15,RANK.EQ($N1,$N$1:$N$15))
W1:AB15	W1	=IFERROR(INDEX(A$1:A$11,IF(P1=0,"",INDEX(P$17:P$27,SUM(P$1:P1)))),"")
N1:N15,M17:M27,H17:H25,I17:L26	N1	=RAND()
P17:U27	P17	=IFERROR(RANK.EQ(H17,H$17:H$27),"")

Upvote 0

L

Legacy 510990

Guest

Oct 13, 2023

#9

Anonymous1378 said:
If you don't mind lots of helper columns (doubt it can be avoided too much with your excel version anyway?), a simplistic way would be as follows

Book1
A B C D E F G H I J K L M N O P Q R S T
1 1 10 20 30 40 50 0.478953016 0.445898762 0.207568396 0.42326561 0.022430149 0.007420165 8 15 37
2 2 11 21 31 41 51 0.573818637 0.233776727 0.49434981 0.268389865 0.471740116 0.139971316 5 28 46
3 3 12 22 32 42 52 0.047451791 0.527797644 0.431925628 0.739440245 0.254980617 0.063950828 12 29 34 49
4 4 13 23 33 43 53 0.34784856 0.071928512 0.145877897 0.374548757 0.962761498 0.172309897 38 41
5 5 14 24 34 44 54 0.50772067 0.520156732 0.801446648 0.086887092 0.217943781 0.583485623 7 13 23 54
6 6 15 25 35 45 55 0.313396535 0.986005705 0.892855925 0.553695896 0.205011902 0.191385256 10 21 35 59
7 7 16 26 36 46 56 0.147305124 0.214443146 0.926437987 0.281704509 0.294081145 0.723309384 20 39 48 51
8 8 17 27 37 47 57 0.843610273 0.562216357 0.675348561 0.064756768 0.659013504 0.770834745 3 11 25 45 50
9 9 18 28 38 48 58 0.292011465 0.275161918 0.733059008 0.929769705 0.222386769 0.686586284 19 24 30 53
10 19 29 39 49 59 0.858317974 0.462239863 0.426954862 0.825227865 0.69167851 0.45534515 2 14 33 44 57
11 0.681453875 0.312366786 0.280677371 0.479231093 0.333253678 0.705283746 4 17 36 47 52
12 0.024146126 0.246517762 0.581507217 0.880174048 0.965082751 0.148943134 26 31 40
13 0.535327216 0.276490979 0.563810985 0.051698095 0.791400088 0.473947441 6 18 27 43 55
14 0.95978952 0.321880592 0.850598119 0.869907678 0.874770715 0.212213794 1 16 22 32 42 58
15 0.437195596 0.110121073 0.203765553 0.128308331 0.08609667 0.467480586 9 56
Sheet1
Cell Formulas
Range Formula
H1:M15 H1 =RAND()
O1:T15 O1 =INDEX(A$1:A$15,RANK.EQ(H1,H$1:H$15))&""

Thank you very much for helping me

Upvote 0

L

Legacy 510990

Guest

Oct 13, 2023

#10

Anonymous1378 said:
Yeah I'm not entirely sure how I would get around that without a fixed combination of row types, which are then re-ordered at random.

Book1.xlsx
A[ /XH][XH=w:15]B C D E[/XH ][XH=w:15]F G H I K L M N O P Q R S T U V W X Y Z[ /XH][XH=w:16]AA AB
1 1 10 20 30 40 50 1 1 1 1 0.744[ /XD][XD] 1 0 0 1 1 1 1 [FORMULA ='=IFERROR(INDEX(B$1:B$11,IF(Q1=0,"",INDEX(Q$17:Q$27,SUM(Q$1:Q1)))),"")'][/FORMULA] 35 48 51
2 2 11 21 31 41 51 1 1 1 1 0.901[/FORMULA ][/XD][XD][/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(H$1:H$15,RANK.EQ($N2 ,$N$1:$N$15))]0 1 1 0 16 27 43 55
3[/XH ][XD=h:c|v:m|ch:13|fz:10pt|cls:ww]3[/XD][XD=h:c|v:m|fz:10pt|cls:ww]12[ /XD][XD=h:c|v:m|fz:10pt|cls:ww]22[/XD][XD=h:c|v:m|fz:10pt|cls:ww]32[/XD ][XD=h:c|v:m|fz:10pt|cls:ww]42[/XD][XD=h:c|v:m|fz:10pt|cls:ww]52[/XD][ XD[/XD][XD][/XD][XD=h:c|fz:10pt]1[/XD][XD=h:c|fz:10pt]1[/XD][XD][/ XD][XD=h:c|fz:10pt]1[/XD][XD=h:c|fz:10pt]1[/XD][XD=fz:10pt|cls:fx]0,09[/XD][XD][/XD][XD=h:c|v:m|fz:10pt|cls:fx ww]1[/XD][XD=h:c|v:m|fz:10pt|cls:fx ww]1[/XD][XD=h:c|v:m|fz:10pt| cls:fx ww]1[/XD][XD=h:c| v:m|fz:10pt|cls:fx ww]0[/XD ][XD=h:c|v:m|fz:10pt|cls:fx ww] 1[/XD][XD=h:c|v:m|fz:10pt|cls:fx ww]0[/XD][XD][/XD][XD=fz:10pt|cls:fx]7[/XD][XD=fz:10pt|cls :fx]21[/XD][XD=h:l|fz:10pt|cls:fx] [/XD][ XD=fz:10pt|cls:fx]46[/XD][XD=h:l|fz:10pt|cls:fx] [/XD]
4 4[/XD ][XD=h:c|v:m|fz:10pt|cls:ww]13 23 43 53 1[/XD ][XD=h:c|fz:10pt]1 1 1 0,062 1 1 0 0 9 19 20 37
5 5[ /XD][XD=h:c|v:m|fz:10pt|cls:ww]14 24[/XD ][XD=h:c|v:m|fz:10pt|cls:ww]34 44 1[ /XD][XD=h:c|fz:10pt]1 1 1[/XD ][XD] 0,481 1[/XD ][XD=h:c|v:m|fz:10pt|cls:fx ww] 1 0 1 0 1 14 [/ XD][XD=fz:10pt|cls:fx]32 53
6 6[/ XD][XD=h:c|v:m|fz:10pt|cls:ww]15 25 35 45 1[ /XD][XD] 1 1 0,84 0 1 1 1 0 1 15 26 30 [/XD ][XD=fz:10pt|cls:fx]57
7 7 16 26 36 46 56 1 1 1 1 0,56[/XD ][XD] 1 0 1 1 0 [/ XD][XD=fz:10pt|cls:fx]25 33 60
8 8 17 27 37 47 57 1 1 1 1 0,731 1 0 1 0 1 1 4 22 [FORMULA ='=IFERROR(INDEX(D$1:D$11,IF(S8=0,"",INDEX(S$17:S$27,SUM(S$1:S8)))),"")'][/FORMULA] 40 54
9 9 18 28 38 48 58[ /XD][XD] 1 1 1 1 0.758 0 1 1 [ CÔNG THỨC==INDEX(K$1:K$15,RANK.EQ($N9,$N$1:$N$15))]1[/FORMULA] 1 0[ /XD][XD] 12 39 41[ /XD][XD=h:l|fz:10pt|cls:fx]
10 19 29 49 59 1[/XD ][XD=h:c|fz:10pt]1 1 1 0.483 0 0 1 1 2 11 44 59
11 [/XD ][XD 60 [ /XD][XD=h:c|v:m|fz:10pt|cls:ww]1 1 1 1 0,557 1 0 1 0[/FORMULA='=IFERROR(INDEX(A $1:A$11,IF(P11=0,"",INDEX(P$17:P$27,SUM(P$1:P11)))),"")']6 23 38 49
12 [ /XD][XD] 1 1 1 0,36 0 1 0 1 34 50
13 [ /XD][XD=h:c|v:m|fz:10pt|cls:ww]1 1 1 1 0,326 1 1 1 1[/FORMULA='=IFERROR(INDEX(A $1:A$11,IF(P13=0,"",INDEX(P$17:P$27,SUM(P$1:P13)))),"")']8 10 24 [/XD ][XD=fz:10pt|cls:fx]58
14 [/ XD[XD] 1 1 1 1 0.911[/XD ][XD] 0 1 0 1 1 18[/ XD][XD=h:l|fz:10pt|cls:fx] 31 42 52
15 [ /XD[XD] 1 1 1[/XD ][XD=h:c|fz:10pt]1 0,945 0 0 1 1 1[ /XD][XD] [/ XD][XD=fz:10pt|cls:fx]29 36 [ CÔNG THỨC='=IFERROR(INDEX(E$1:E$11,IF(T15=0,"",INDEX(T$17:T$27,SUM(T$1:T15)))),"")']45[/ CÔNG THỨC 56
16
17 0.912 0,248 0,136 0,474 0,207 0.937 1 7 8 6 9 [ CÔNG THỨC='=IFERROR(RANK.EQ(M17,M$17:M$27),"")']2[/FORMULA]
18 [XD] 0.329 0.789[/ CÔNG THỨC[/XD][XD=fz:10pt|cls:fx][FORMULA==RAND()]0,767 0,399 0,775 0,628 7 4 2 8 6 [/XD ][XD]
19 0.223 0.121 0,895 0,762 [ CÔNG THỨC==RAND()]0,505[/FORMULA] 0,731 [/XD ][XD] 9[ /FORMULA][/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(I19,I$17:I$27),"")']10[/ CÔNG THỨC[/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(J19,J$17:J$27),"")']1[/FORMULA ][/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(K19,K$17:K$27),"")']3 7[ /XD][XD=h:c|fz:10pt|cls:fx]4[/ XD[XD] [/XD ]
20 0.607 0.642 0.179[ /XD][XD=fz:10pt|cls:fx]0.909 0,908 0,544 [/XD ][XD=h:c|fz:10pt|cls:fx]3 5 1 8 [/ XD[XD]
22 0,556 0,804 0,423 0,031 0,567 0,652[/ XD][XD] 4 3 3 10 5 2 2[ /FORMULA][/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(J23,J$17:J$27),"")']9[/ CÔNG THỨC][/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(K23,K$17:K$27),"")']9 10 10
24 [/XD ][XD 0.472[ /FORMULA][/XD][XD=fz:10pt|cls:fx][FORMULA==RAND()]0.225 0,411 0,552 0,291 0,985 8 4 5[ /FORMULA][/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(L24,L$17:L$27),"")']8[/ CÔNG THỨC[/XD][XD=h:c|fz:10pt|cls:fx][FORMULA='=IFERROR(RANK.EQ(M24,M$17:M$27),"")']1[/FORMULA [/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD] [/XD][/XR][XR][XH]25[/XH][XD=ch:13][/XD][XD][/XD][XD][/XD][XD][/XD ][XD[/XD][XD][/XD][XD][/XD][XD=fz:10pt|cls:fx][FORMULA==RAND()]0.327 0.914 0.289[/ CÔNG THỨC[/XD][XD=fz:10pt|cls:fx][FORMULA==RAND()]0.803 0,818 0,472 8[ /XD][XD=h:c|fz:10pt|cls:fx]1[/ XD][XD=h:c|fz:10pt|cls:fx]5[/XD ][XD=h:c|fz:10pt|cls:fx]2 3 26 [/XD ][XD 0.217 0,03 0,468[/ CÔNG THỨC[/XD][XD=fz:10pt|cls:fx][FORMULA==RAND()]0.534 0,886 9 [ CÔNG THỨC='=IFERROR(RANK.EQ(J26,J$17:J$27),"")']10[/FORMULA] [FORMULA ='=IFERROR(RANK.EQ(K26,K$17:K$27),"")']7[/FORMULA] 6 3 [/XD ][XD
27 [XD] 0,578 7
Sheet1
[ XH=w:31]J[/XH] [ XD][/XD] [ XD=h:c|v:m|fz:10pt|cls:fx ww] 1[ /FORMULA][/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(M$1:M$15,RANK.EQ($N2,$N$1: $N$15))]1 [/XD] [XH] [ XD=h:c|v:m|fz:10pt|cls:ww]33[/XD] [ XD=h:c|v:m|fz:10pt|cls:fx ww] 1[ /FORMULA][/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(J$1:J$15,RANK.EQ($N4,$N$1: $N$15))]1 [/XD] [ XD=h:c|v:m|fz:10pt|cls:ww]54[/XD] [XD =fz:10pt|cls:fx] 3 [/XD] [XD =h:c|v:m|fz:10pt|cls:ww]55[/XD] [ XD=h:c|fz:10pt]1[/XD] [XD ][/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 1[/ CÔNG THỨC[/XD][XD][/XD][XD=fz:10pt|cls:fx][FORMULA='=IFERROR(INDEX(A$1:A$11,IF(P7=0,"",INDEX( P$17:P$27,SUM(P$1:P7))),"")']5 [/XD] [XD ][/XD] [ XD=fz:10pt|cls:fx] 28 [/XD] [XD =h:c|v:m|fz:10pt|cls:ww]39[/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 1[/ CÔNG THỨC[/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(I$1:I$15,RANK.EQ($N10,$N$1:$N$15))]1 [/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 1[/ CÔNG THỨC[/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(L$1:L$15,RANK.EQ($N11,$N$1:$ N$15))]1 [/XD] [ XD=h:c|fz:10pt]1[/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 1[/ CÔNG THỨC[/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(M$1:M$15,RANK.EQ($N12,$N$1:$ N$15))]1 [/XD] [ XD=fz:10pt|cls:fx] 47 [/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 0[/ CÔNG THỨC[/XD][XD=h:c|v:m|fz:10pt|cls:fx ww][FORMULA==INDEX(L$1:L$15,RANK.EQ($N13,$N$1:$ N$15))]0 [/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 1[/ CÔNG THỨC[/XD][XD][/XD][XD=h:l|fz:10pt|cls:fx][FORMULA='=IFERROR(INDEX(A$1:A$11,IF(P14=0," ",INDEX(P$17:P$27,SUM(P$1:P14)))),"")'] [/XD] [XD =h:c|v:m|fz:10pt|cls:fx ww] 1 [/XD] [ XD[/XD] [XD ][/XD] [XD] [XD ][/XD] [ XD=h:c|fz:10pt|cls:fx] 7 [/XD][XD =h:c|fz:10pt|cls:fx] 1 [/XD] [XR ][XH]21[/XH][XD=ch:13][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD ][/XD][XD][/XD][XD=fz:10pt|cls:fx] 0,532 [/XD][XD=fz:10pt|cls:fx] 0,39 [/XD][XD= fz:10pt|cls:fx] 0,267 [/XD][XD=fz:10pt|cls:fx] 0,707 [ /XD][XD=fz:10pt|cls:fx] 0.851 [/XD][XD=fz:10pt|cls:fx] 0,366 [/XD][XD][/XD][XD][/XD][XD=h:c|fz:10pt|cls:fx] 5 [/XD][XD=h:c|fz:10pt|cls:fx] 6 [/XD][XD=h:c|fz:10pt|cls:fx] 6 [/XD][XD=h:c|fz:10pt|cls:fx] 4 [/XD][XD=h:c|fz:10pt|cls:fx] 2 [/XD][XD=h:c|fz:10pt|cls:fx] 11 [/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/ XD[XD][/XD][XD][/XD][/XR] [ XD[/XD] [ XD=h:c|fz:10pt|cls:fx] 9 [/XD][XD [/XD] [/XR ][XR] [XD [/XD]
Cell Formulas
Range Formula
P1:U15 =INDEX(H$1:H$15,RANK.EQ($N1,$N$1:$N$15))
W1 =IFERROR(INDEX(A$1:A$11,IF(P1=0,"",INDEX(P$17:P $27,SUM(P$1:P1)))),"")
N1:N15,M17:M27,H17:H25,I17:L26 =RAND()
P17:U27 P17[/XD ][XD]=IFERROR(RANK.EQ(H17,H$17:H$27),"")
[XD =fw:b]P1[/XD] [ XD]W1:AB15[/XD] [ XD=fw:b]N1[/XD]
fx] [/XD][XD=h:c|fz:10pt|cls:fx] [/XD][XD=h:c|fz:10pt|cls:fx][FORMULA ='=IFERROR(RANK.EQ(K27,K$17:K$27),"")'][/FORMULA][/XD][XD=h:c|fz:10pt|cls:fx] [/XD][XD=h:c|fz:10pt|cls:fx]7[/XD][XD][/XD][XD][/XD][XD][/XD][ XD[/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE]
Cell Formulas
Range Formula
P1:U15 P1 =INDEX(H$1:H$15,RANK.EQ($N1,$N$1:$N $15))
W1:AB15 W1 =IFERROR(INDEX(A$1:A$11, IF(P1=0,"",INDEX(P$17:P$27,SUM(P$1:P1)))),"")
N1:N15,M17 :M27,H17:H25,I17:L26 N1 =RAND()
P17:U27 P17 =IFERROR(RANK.EQ(H17,H$17:H$27),"")
fx] [/XD][XD=h:c|fz:10pt|cls:fx] [/XD][XD=h:c|fz:10pt|cls:fx][FORMULA ='=IFERROR(RANK.EQ(K27,K$17:K$27),"")'][/FORMULA][/XD][XD=h:c|fz:10pt|cls:fx] [/XD][XD=h:c|fz:10pt|cls:fx]7[/XD][XD][/XD][XD][/XD][XD][/XD][ XD[/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE]
Cell Formulas
Range Formula
P1:U15 P1 =INDEX(H$1:H$15,RANK.EQ($N1,$N$1:$N $15))
W1:AB15 W1 =IFERROR(INDEX(A$1:A$11, IF(P1=0,"",INDEX(P$17:P$27,SUM(P$1:P1)))),"")
N1:N15,M17 :M27,H17:H25,I17:L26 N1 =RAND()
P17:U27 P17 =IFERROR(RANK.EQ(H17,H$17:H$27),"")

Tôi có thể tải file excel trên được không?

Anonymous1378 said:
Yeah I'm not entirely sure how I would get around that without a fixed combination of row types, which are then re-ordered at random.

Book1.xlsx
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB
1 1 10 20 30 40 50 1 1 1 1 0.744 1 0 0 1 1 1 1 35 48 51
2 2 11 21 31 41 51 1 1 1 1 0.901 0 1 1 0 1 1 16 27 43 55
3 3 12 22 32 42 52 1 1 1 1 0.09 1 1 1 0 1 0 7 13 21 46
4 4 13 23 33 43 53 1 1 1 1 0.062 1 1 1 1 0 0 9 19 20 37
5 5 14 24 34 44 54 1 1 1 1 0.481 1 1 0 1 0 1 3 14 32 53
6 6 15 25 35 45 55 1 1 1 1 0.84 0 1 1 1 0 1 15 26 30 57
7 7 16 26 36 46 56 1 1 1 1 0.56 1 0 1 1 0 1 5 25 33 60
8 8 17 27 37 47 57 1 1 1 1 0.731 1 0 1 0 1 1 4 22 40 54
9 9 18 28 38 48 58 1 1 1 1 0.758 0 1 1 1 1 0 12 28 39 41
10 19 29 39 49 59 1 1 1 1 0.483 1 1 0 0 1 1 2 11 44 59
11 60 1 1 1 1 0.557 1 0 1 1 1 0 6 23 38 49
12 1 1 1 1 0.36 0 1 0 1 1 1 17 34 47 50
13 1 1 1 1 0.326 1 1 1 0 0 1 8 10 24 58
14 1 1 1 1 0.911 0 1 0 1 1 1 18 31 42 52
15 1 1 1 1 0.945 0 0 1 1 1 1 29 36 45 56
16
17 0.912 0.248 0.136 0.474 0.207 0.937 1 7 8 6 9 2
18 0.329 0.789 0.767 0.399 0.775 0.628 7 4 2 8 4 6
19 0.223 0.121 0.895 0.762 0.505 0.731 9 10 1 3 7 4
20 0.607 0.642 0.179 0.909 0.908 0.544 3 5 7 1 1 8
21 0.532 0.39 0.267 0.707 0.851 0.366 5 6 6 4 2 11
22 0.556 0.804 0.423 0.031 0.567 0.652 4 3 3 10 5 5
23 0.645 0.848 0.075 0.329 0.018 0.385 2 2 9 9 10 10
24 0.472 0.225 0.411 0.552 0.291 0.985 6 8 4 5 8 1
25 0.327 0.914 0.289 0.803 0.818 0.472 8 1 5 2 3 9
26 0.217 0.03 0.468 0.534 0.886 9 10 7 6 3
27 0.578 7
Sheet1
Cell Formulas
Range Formula
P1:U15 P1 =INDEX(H$1:H$15,RANK.EQ($N1,$N$1:$N$15))
W1:AB15 W1 =IFERROR(INDEX(A$1:A$11,IF(P1=0,"",INDEX(P$17:P$27,SUM(P$1:P1)))),"")
N1:N15,M17:M27,H17:H25,I17:L26 N1 =RAND()
P17:U27 P17 =IFERROR(RANK.EQ(H17,H$17:H$27),"")

Can I download the above excel file?

Upvote 0

You must log in or register to reply here.

Similar threads

K

Question

conditional formatting question

Replies: 2

Views: 161

Mar 16, 2024

kerm007

K

V

Question

Request help in extracting mutiple number groups from specific character positions from a varying number size.

Replies: 3

Views: 136

Mar 6, 2024

Fluff

A

Question

Matching a number sequence within a group of numbers in a cell

Replies: 1

Views: 267

Feb 2, 2024

FormR

Solved

Formula for calculating different dates/ VBA

Replies: 3

Views: 160

Mar 29, 2024

awoohaw

R

Question

Percentile Numbers dont add up...

Replies: 1

Views: 137

Feb 27, 2024

Eric W

Share:

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,215,072

Messages: 6,122,968

Members: 449,095

Latest member: Mr Hughes

Share this page

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Top