I'm rather new to excel and I want to use excel or perhaps another program to subtract on a fixed amount but randomly. It is like n1 n2 n3 = 300 But I want n1 and n2 and n3 to be different numbers hence not division
Examples 150 75 75 = 300
or
100 100 100 = 300
or
50 100 150 = 300
A function to subtract a fixed amount but random subtraction I'm still quite confused on how to do this on excel, sorry for my bad English and explaination Please help.
CodePudding user response：
If it is always 3 numbers:
The first number we use:
=RANDBETWEEN(1,A1)
Then the second:
=RANDBETWEEN(1,A1-B1)
Then the third is just the remainder:
=A1-B1-C1
CodePudding user response：
In excel you have your numbers in rows and columns I would enter 100, 100 , 100 in separate columns of the same row and then use this formula
=(A1 B1 C1).
Meaning row A1 100, row B1 100, row C1 100. The answer should be 300 if you want to subtract just use the minus sign instead of the plus sign:
formula =(A1-B1-C1).
CodePudding user response：
Try:
=LET(A,RANDBETWEEN(0,300),B,RANDBETWEEN(0,300-A),HSTACK(A,B,300-SUM(A,B)))
Or, if no HSTACK() available:
=LET(A,RANDBETWEEN(0,300),B,RANDBETWEEN(0,300-A),CHOOSE({1,2,3},A,B,300-SUM(A,B)))
I'm just unsure if pure mathematically this is as random as can be. Would it be 'more random' if one would list all possible permutation of 1-300 that would add up to 300 and randomly pick a solution from this list?
CodePudding user response：
One way to approach this if you want two numbers to always add up to the same value, is to modify each value with the same amount, by adding to one value and subtracting from the other
100 = n1 n2 = (n1 a) (n1-a)
where a is any random number.
To extend this to three numbers, you can use two artbitrary numbers a and b to do this following
100 = n1 n2 n3 = (n1 a) (n2-a b) (n3-b)
The simplified approach to this it to pick completely random n2 and n3 and let n1 pick up the difference
100 = (100-n2-n3) n2 n3
To do this in Excel, use the =RANDBETWEEN() function for n2 and n3 and then for n1 just subtract from 100
=100 - SUM(n2,n3)