How Excel determines that the sum of several unknowns is exactly equal to the given summation value

First, to understand the RAND function

Because we have to find these values, we do not know exactly which number, and only know how many of these numbers are equal to how much, that is only know and the value does not know what these numbers are.

Therefore, you must use the Rand random number to solve such an unknown problem.

The use of RAND is simple by: =rand ()

Its value range is: (0,1)

For example: the formula =rand () *10 the range of values obtained by the formula is (0,10], that is, any number that is greater than 0 and less than or equal to 10.

With the rand random number, we can deduce the first number by the given sum value, assuming m, and then deduce the second, third, fourth ... Number of Nth.

Second, the theory of several unknowns is deduced by the sum value m

Suppose that given a and value m, according to the sum of the values, we calculate three numbers, which add up to just M.

In view of this condition, we all understand that these three number of possible cases, is quite a lot, not only a set of numbers, but a sequence of data.

Calculation of the first number: The theoretical basis is that the number can not be greater than M

So we use the formula Num1=rand () *m

Let's analyze the range of values for the first number, the range of RAND () is (0,1], multiply it by the value M, then the first number is the range: (0,m)

If the first number is not M, then the second third number must not be 0, otherwise, the second third number must be 0

The calculation of the second number: The theoretical basis is that the sum value minus the first number and then divided by the random value,

namely: Num2==rand () * (M-NUM1)

The value range of the second number is analyzed below, greater than 0 and less than or equal to (M-NUM1)

Calculation of the third number: since it is the last number, therefore, the third number num3=m-num1-num2

Naturally, the value range of the third number is greater than 0 or less than or equal to (m-num1-num2).

In the above reasoning process, it all seems to be unknown, as if there is no definite solution, but don't forget, RAND () and m in the specific application process, is completely a specific number, therefore, the reasoning of the first, second, third ... The number of nth is entirely a specific number.

Below, we give a concrete example, so that everyone's ideas to clear up.

Suppose that given a sum value of M is 80, the requirement is to give three numbers through Excel, and these three numbers are exactly equal to 80, and the three numbers are determined.

These three numbers, in the end, the solution has infinite, that is to say, these three unknowns, there are infinite solutions, to a few can, below, we give the specific formula code and a few groups of solutions.

The formula for the first number NUM1 is: =rand () *80

The formula for the second number Num2: =rand () * (80-NUM1)

The third number Num3 formula is: =80-num1-num2

If you use the Fill method in the spreadsheet, you can get an infinite solution. This is the answer, below, for you to post a few sets of solutions.

NUM1 Num2 Num3

15.79585527 0.557776878 63.64636785

13.65591211 66.0342379 0.309849991

69.89598614 7.781773093 2.322240765

78.74553578 0.209932603 1.044531618

17.43271997 53.86973039 8.697549647

45.66567338 20.4002065 13.93412011

Third, knowledge expansion

① How to calculate the number of n by sum

Know m, how to determine the number of n, so that the number of N and the value of just m, the number of n the formula how to calculate?

First Number: RAND () *m

Second number: RAND () * (M-NUM1)

Third number: RAND () * (M-NUM1-NUM2)

Number fourth: RAND () * (M-NUM1-NUM2-NUM3)

......

Number of N-1: RAND () * (m-num1-num2-num3-......-Num (n-2))

Number n: m-m-num1-num2-num3-......-Num (n-2)-num (n-1)

This is the general formula. is based on the above theory and practice inferred that, after verification, no errors found.

② a few integers based on M rather than real numbers

If you know the M sum value, how do I get a few numbers, and these numbers must be integers instead of real numbers with a decimal point?

Use the Int () function to resolve this type of problem. The general formula is as follows:

First number: Int (RAND () *m)

Second number: Int (RAND () * (M-NUM1))

Third number: Int (RAND () * (M-NUM1-NUM2))

Number fourth: Int (RAND () * (M-NUM1-NUM2-NUM3))

......

Number of N-1: Int (RAND () * (m-num1-num2-num3-......-Num (n-2))

Number n: m-m-num1-num2-num3-......-Num (n-2)-num (n-1)