Excel- Split a number

Occasional Contributor

Dear Sir,

I am facing problem in excel for splitting a number into different random parts


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.



Dr. Ashish Sharma

Assistant Professor

10 Replies
best response confirmed by ashunjoy_1911 (Occasional Contributor)



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


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.

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.





Thank you very much, Sir.



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)



Thank you Assaf


Actually the excel shows me error in the formula as I am using Office 2016

In 2016 "IFS" function is not working. "IFS" function is introduced after 2016.


Error- =_xlfn.IFS($B$1=0,0,$I16<>$A16,RANDBETWEEN(B$3,B$4),TRUE,B16)


Can you provide nested IF, Please



Please find a modified version attached

I had fun doing this project





I am really grateful that you have taken up this task.


You really become an escape boat for me.



Dr. Ashish



Hey Ramiz, can you help me out?

I tried your formula, but it does not work here.