Subtraction generator formula | Randomly subtraction in excel

[Aly01]

Hello,

I want to randomly subtract in excel using formula =RANDBETWEEN(1,9), but I don't want the result to be a negative number.
Example: 2 - 5 = -3 -> but I don't want that.
Can you help me? I'm not that good with excel ...
Thank you.

 

[Phuoc]

If you don't want the result to be a negative number, use the ABS() function.

=ABS(2-5)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have the LET function then try ..

22 12 18.xlsm
	A
1	7 - 5 = 2
2	9 - 7 = 2
3	5 - 4 = 1
4	2 - 1 = 1
5	2 - 1 = 1
6	2 - 1 = 1
7	1 - 1 = 0
8	5 - 1 = 4
9	1 - 1 = 0
10	8 - 1 = 7
Subtract

Cell Formulas
Range	Formula
A1:A10	A1	=LET(a,RANDBETWEEN(1,9),b,RANDBETWEEN(1,a),a&" - "&b&" = "&a-b)

 

[Aly01]

Thank you, but unfortunately I have no idea how to use LET formula.

1 digit subtraction.xlsx
	A	B	C	D	E
1
2	4	-	9	=
3
4	4	-	5	=
5
6	8	-	9	=
7
8	2	-	6	=
9
10	7	-	5	=
11
12	2	-	6	=
13
Sheet2

Cell Formulas
Range	Formula
A2,C12,A12,C10,A10,C8,A8,C6,A6,C4,A4,C2	A2	=RANDBETWEEN(1,9)

 

[Peter_SSs]

Thanks for adding your version details.

unfortunately I have no idea how to use LET formula.

Just copy from my post (if you have TEXTSPLIT since you apparently want each bit in a separate cell)

22 12 18.xlsm
	A	B	C	D	E
1	9	-	7	=
2	2	-	1	=
3	9	-	4	=
4	8	-	2	=
5	9	-	2	=
6	1	-	1	=
7	7	-	7	=
8	2	-	1	=
9	2	-	1	=
10	9	-	5	=
11	1	-	1	=
Subtract

Cell Formulas
Range	Formula
A1:D11	A1	=LET(a,RANDBETWEEN(1,9),b,RANDBETWEEN(1,a),TEXTSPLIT(a&" - "&b&" ="," "))
Dynamic array formulas.

 

[Peter_SSs]

if you have TEXTSPLIT

If you don't have TEXTSPLIT, then you could try this one.

22 12 18.xlsm
	A	B	C	D	E
1	4	-	4	=
2	6	-	4	=
3	2	-	2	=
4	3	-	3	=
5	5	-	5	=
6	6	-	4	=
7	4	-	4	=
8	9	-	6	=
9	8	-	8	=
10	7	-	6	=
11	3	-	2	=
Subtract (2)

Cell Formulas
Range	Formula
A1:D11	A1	=LET(a,RANDBETWEEN(1,9),b,RANDBETWEEN(1,a),MID(CONCAT(a,"-",b,"="),SEQUENCE(,4),1))
Dynamic array formulas.

 

[Phuoc]

Or try

Book1
	A	B	C	D
2	2	-	1	=
3	9	-	5	=
4	3	-	3	=
5	6	-	1	=
6	7	-	4	=
7	5	-	3	=
8	2	-	2	=
9	4	-	3	=
10	6	-	3	=
11	3	-	3	=
12	8	-	4	=
Sheet1

Cell Formulas
Range	Formula
A2:A12	A2	=RANDBETWEEN(1,9)
C2:C12	C2	=RANDBETWEEN(1,A2)

 

[Aly01]

If you don't have TEXTSPLIT, then you could try this one.

22 12 18.xlsm
	A	B	C	D	E
1	4	-	4	=
2	6	-	4	=
3	2	-	2	=
4	3	-	3	=
5	5	-	5	=
6	6	-	4	=
7	4	-	4	=
8	9	-	6	=
9	8	-	8	=
10	7	-	6	=
11	3	-	2	=
Subtract (2)

Cell Formulas
Range	Formula
A1:D11	A1	=LET(a,RANDBETWEEN(1,9),b,RANDBETWEEN(1,a),MID(CONCAT(a,"-",b,"="),SEQUENCE(,4),1))
Dynamic array formulas.

It took me a while to figure it, but now it works. Both formulas works.
Thank you. Thank you. Thank you.
Thank you Peter.

 

[Aly01]

Or try

Book1
	A	B	C	D
2	2	-	1	=
3	9	-	5	=
4	3	-	3	=
5	6	-	1	=
6	7	-	4	=
7	5	-	3	=
8	2	-	2	=
9	4	-	3	=
10	6	-	3	=
11	3	-	3	=
12	8	-	4	=
Sheet1

Cell Formulas
Range	Formula
A2:A12	A2	=RANDBETWEEN(1,9)
C2:C12	C2	=RANDBETWEEN(1,A2)

SO EASY. It works great.
Thank you Phuoc.

​

 

[Peter_SSs]

SO EASY.

I agree.
I didn't even bother to try that earlier as I was sure that column A would likely recalculate at the wrong time and create a number smaller than column C or that it would create some kind of endless recalculation. Just goes to show the need to actually test.