Forum Discussion
Excel- Split a number
Dear Sir,
I am facing problem in excel for splitting a number into different random parts
Like
I want to split 45 into 7 random parts
"1st part between 0-12"
" 2nd part between 0-12"
" 3rd part between 0-12"
" 4th part between 0-6"
" 5th part between 0-6"
" 6th part between 0-6"
" 7th part between 0-6"
All numbers should be randomly selected and the sum of all 7 parts must be 45. Likewise, I have several numbers to divide like 45,55,32,33, etc but the split will be in 7 parts as mentioned.
Please help me, if you can help me in the splitting of one number I will do the rest.
Thanks
Dr. Ashish Sharma
Assistant Professor
Dear Dr. Sharma
Please find a file that has a VBA script.
input your number in cell A1 (for example 45)
the function will use randbetween... until the sum will be 45
if you want 55, just change the value in cell A1
best regards
10 Replies
- Is this something you will only need to do once for a few numbers, or will you need to do this repeatedly?
I would probably generate all of the number combinations, then see how many sum to the target value, and then get a random number between 1 and the number of combinations equaling the target value to randomly pick one of them.
If you need to do this repeatedly, I would probably generate all of the number combinations and save them in a table with a total column (which I don't think will fit in a worksheet, may need to put it in a text/csv file and read it in). That way, you wouldn't have to generate all of the number combinations every time you needed to do this and save quite a bit of time. Hello ashunjoy_1911,
I'm not sure that I fully understand your problem. It could be possible that not all 7 parts are random. For example, say random numbers of the first 5 parts were 12, 10, 12, 5, 6. The sum would already be 45 and thus the final 2 parts must be 0. This would make the final two parts non-random as they are dependent on the sum of the previous parts.
Dear Dr. Sharma
Please find a file that has a VBA script.
input your number in cell A1 (for example 45)
the function will use randbetween... until the sum will be 45
if you want 55, just change the value in cell A1
best regards
Sir, I got stuck in my original file.
Can you give a generous solution for the attached file?
Press F9 to generate solutions
accepted solutions will become green
keep pressing F9 until all the column becomes green
hope this solution work, I dunno what to do with fractions (round up or down)
