Excel- Split a number

[ashunjoy_1911]

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

[#2 · Accepted solution]

@ashunjoy_1911 

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

[#3]

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.

[#4]

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.

[#5]

@Ramiz_Assaf 

Perfect  

Thank you very much, Sir.

[#6]

@Ramiz_Assaf 

Sir, I got stuck in my original file.

Can you give a generous solution for the attached file? 

[#7]

@ashunjoy_1911 

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)

[#8]

@Ramiz_Assaf 

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

[#9]

@ashunjoy_1911 

Please find a modified version attached

I had fun doing this project

[#10]

@Ramiz_Assaf 

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

You really become an escape boat for me.

Regards

Dr. Ashish

[#11]

@Ramiz_Assaf 

Hey Ramiz, can you help me out?

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

CLOs