Forum Discussion
Solved
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 ...
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
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.
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.