In need of help

[Renaye]

Hi

I want to make new lotto combinations from one line say

35

30

17

43

34

45

15

14

23

<tbody>
</tbody>

Knowing that the smallest number is one and the largest is 45, I wish to +1 to each of the above and continue.

Should look something like this

36	31	18	44	35	1	16	15	24
37	32	19	45	36	2	17	16	25
38	33	20	1	37	3	18	17	26
39	34	21	2	38	4	19	18	27
40	35	22	3	39	5	20	19	28
41	36	23	4	40	6	21	20	29
42	37	24	5	41	7	22	21	30
43	38	25	6	42	8	23	22	31
44	39	26	7	43	9	24	23	32
45	40	27	8	44	10	25	24	33
1	41	28	9	45	11	26	25	34
2	42	29	10	1	12	27	26	35
3	43	30	11	2	13	28	27	36
4	44	31	12	3	14	29	28	37
5	45	32	13	4	15	30	29	38
6	1	33	14	5	16	31	30	39
7	2	34	15	6	17	32	31	40
8	3	35	16	7	18	33	32	41
9	4	36	17	8	19	34	33	42
10	5	37	18	9	20	35	34	43
11	6	38	19	10	21	36	35	44
12	7	39	20	11	22	37	36	45
13	8	40	21	12	23	38	37	1
14	9	41	22	13	24	39	38	2
15	10	42	23	14	25	40	39	3
16	11	43	24	15	26	41	40	4
17	12	44	25	16	27	42	41	5
18	13	45	26	17	28	43	42	6
19	14	1	27	18	29	44	43	7
20	15	2	28	19	30	45	44	8
21	16	3	29	20	31	1	45	9
22	17	4	30	21	32	2	1	10
23	18	5	31	22	33	3	2	11
24	19	6	32	23	34	4	3	12
25	20	7	33	24	35	5	4	13
26	21	8	34	25	36	6	5	14
27	22	9	35	26	37	7	6	15
28	23	10	36	27	38	8	7	16
29	24	11	37	28	39	9	8	17
30	25	12	38	29	40	10	9	18

<colgroup><col width="56" span="9" style="width:42pt"> </colgroup><tbody>
</tbody>

But instead it looks like this

36	31	18	44	35	46	16	15	24
37	32	19	45	36	47	17	16	25
38	33	20	46	37	48	18	17	26
39	34	21	47	38	49	19	18	27
40	35	22	48	39	50	20	19	28
41	36	23	49	40	51	21	20	29
42	37	24	50	41	52	22	21	30
43	38	25	51	42	53	23	22	31
44	39	26	52	43	54	24	23	32
45	40	27	53	44	55	25	24	33
46	41	28	54	45	56	26	25	34
47	42	29	55	46	57	27	26	35
48	43	30	56	47	58	28	27	36
49	44	31	57	48	59	29	28	37
50	45	32	58	49	60	30	29	38
51	46	33	59	50	61	31	30	39
52	47	34	60	51	62	32	31	40
53	48	35	61	52	63	33	32	41
54	49	36	62	53	64	34	33	42
55	50	37	63	54	65	35	34	43
56	51	38	64	55	66	36	35	44
57	52	39	65	56	67	37	36	45
58	53	40	66	57	68	38	37	46
59	54	41	67	58	69	39	38	47
60	55	42	68	59	70	40	39	48
61	56	43	69	60	71	41	40	49
62	57	44	70	61	72	42	41	50
63	58	45	71	62	73	43	42	51
64	59	46	72	63	74	44	43	52
65	60	47	73	64	75	45	44	53
66	61	48	74	65	76	46	45	54
67	62	49	75	66	77	47	46	55
68	63	50	76	67	78	48	47	56
69	64	51	77	68	79	49	48	57
70	65	52	78	69	80	50	49	58
71	66	53	79	70	81	51	50	59
72	67	54	80	71	82	52	51	60
73	68	55	81	72	83	53	52	61
74	69	56	82	73	84	54	53	62
75	70	57	83	74	85	55	54	63

<colgroup><col width="56" span="9" style="width:42pt"> </colgroup><tbody>
</tbody>

Is there a way that I can do this and say when the number hits 46 it changes to 1 again and continues to cycle through until I have enough combinations ?

I am extremely new at excel, I'm a excel virgin so I would appreciate if there was a formula that I could cut and paste into the excel formula bar.

Thanking you for your time, patients and understanding

 

[AliGW]

In A2 copied across and down:

=IF(A1=45,1,A1+1)

 

[Canapone]

Hi All

=MOD(A1,45)+1


I like AliGW's approach.

 

[Renaye]

Thank you so much it works great. Is it possible to do this with negative and say when it hits zero it goes to 1 ?

=IF(A1=1,45,A1-1) <- would this work ?

 

[AliGW]

If you want zero, use this instead (I learnt this myself today):

=MOD(A1+1,45)

 

[Renaye]

Again this works great but I don't really need the zero. At the moment it is going

42
43
44
45
0
1
2
3

<colgroup><col width="56" style="width:42pt"> </colgroup><tbody>
</tbody>

where I need it as

42

43

44

45

1 2 3 4 5

<colgroup><col width="56" style="width:42pt"> </colgroup><tbody>
</tbody>

Also with the original question, I have found that once it goes over 250 combinations it starts to go a rye, meaning instead of going to 1 it will go to 47 or so.

 

[Tetra201]

Try these formulas:

For adding a 1: =A1+1-(A1=45)*45
For subtracting a 1: =A1-1+(A1=1)*45

 

[AliGW]

You are doing something wrong! My original formula does what you want:

=IF(A1=45,1,A1+1)

Also with the original question, I have found that once it goes over 250 combinations it starts to go a rye, meaning instead of going to 1 it will go to 47 or so.

It will not go awry if you just copy it down.

 

[AliGW]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I
1	35	30	17	43	34	45	15	14	23
2	36	31	18	44	35	1	16	15	24
3	37	32	19	45	36	2	17	16	25
4	38	33	20	1	37	3	18	17	26
5	39	34	21	2	38	4	19	18	27
6	40	35	22	3	39	5	20	19	28
7	41	36	23	4	40	6	21	20	29
8	42	37	24	5	41	7	22	21	30
9	43	38	25	6	42	8	23	22	31
10	44	39	26	7	43	9	24	23	32
11	45	40	27	8	44	10	25	24	33
12	1	41	28	9	45	11	26	25	34
13	2	42	29	10	1	12	27	26	35
14	3	43	30	11	2	13	28	27	36
15	4	44	31	12	3	14	29	28	37
16	5	45	32	13	4	15	30	29	38
17	6	1	33	14	5	16	31	30	39
18	7	2	34	15	6	17	32	31	40
19	8	3	35	16	7	18	33	32	41
20	9	4	36	17	8	19	34	33	42
21	10	5	37	18	9	20	35	34	43
22	11	6	38	19	10	21	36	35	44
23	12	7	39	20	11	22	37	36	45
24	13	8	40	21	12	23	38	37	1
25	14	9	41	22	13	24	39	38	2
26	15	10	42	23	14	25	40	39	3
Sheet2

Cell Formulas
Range	Formula
A2	=IF(A1=45,1,A1+1)
B2	=IF(B1=45,1,B1+1)
C2	=IF(C1=45,1,C1+1)
D2	=IF(D1=45,1,D1+1)
E2	=IF(E1=45,1,E1+1)
F2	=IF(F1=45,1,F1+1)
G2	=IF(G1=45,1,G1+1)
H2	=IF(H1=45,1,H1+1)
I2	=IF(I1=45,1,I1+1)

 

[Renaye]

Hi AliGW

It was my fault, I opened up a new excel spreadsheet and just tried it again and it is working perfectly. Thank you