In this post we will see how to split a number into N random numbers in excel.
We will create a set of N random numbers whose sum will always be equal to a constant number, for the understanding purpose we will divide the number 80 into 10 random numbers and the sum of those 10 numbers will always be equal to 80
We will use a combination of formula to create random number whose sum will always be equal to a constant number.
RAND() function will generate N numbers (8 in our case) in column A, then, in column B we will use the formula B1=A1/SUM(A:A)*80 (80 is the sum of all the random numbers that we would create).
Step 1
Enter =RAND() in A1, then drag it down to A8 to create 8 random.
Step 2
Enter formula =A1/SUM (A: A)*80 in B1 and drag it to B10. B1:B10 now contains 10 random numbers that sum up to 80.
By above method we created N random numbers whose sum will always be a constant value.
Hope this helped.
