Determining random numbers that add up to a predetermined sum

%3CLINGO-SUB%20id%3D%22lingo-sub-2192448%22%20slang%3D%22en-US%22%3EDetermining%20random%20numbers%20that%20add%20up%20to%20a%20predetermined%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2192448%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3BMy%20goal%20is%20to%20create%20a%20random%20set%20of%20numbers%20that%20add%20up%20to%20a%20particular%20total.%20In%20the%20image%20below%2C%20you'll%20see%20a%20column%20of%2010%20numbers%20and%20its%20total%20in%20A12.%20What%20function%20would%20I%20need%20to%20insert%20in%20order%20to%20randomize%20the%2010%20numbers%2C%20but%20still%20end%20up%20with%20a%20total%20of%20310%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22zh.jpeg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261510i82B99BB42B3D6868%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22zh.jpeg%22%20alt%3D%22zh.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help..%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2192448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2192749%22%20slang%3D%22de-DE%22%3ESubject%3A%20Determining%20random%20numbers%20that%20add%20up%20to%20a%20predetermined%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2192749%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F643-excel-random-number.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20insert%20random%20(integer)%20numbers%20between%20two%20numbers%20without%20repeats%20in%20Excel%3F%3C%2FA%3E%3C%2FP%3E%3CP%3ECclick%20on%20header%20for%20more%20infos)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20method%20will%20apply%20the%20RAND%20function%20and%20RANDBETWEEN%20function%20to%20insert%20random%20numbers%20between%20two%20numbers%20into%20a%20specified%20range%20in%20Excel.%20Please%20view%20below%20formulas%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EMerci%20pour%20votre%20patience%20et%20votre%20temps.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EJ%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Ee%20sais%20que%20je%20ne%20sais%20rien%20(Socrate)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Hello Everyone,

 My goal is to create a random set of numbers that add up to a particular total. In the image below, you'll see a column of 10 numbers and its total in A12. What function would I need to insert in order to randomize the 10 numbers, but still end up with a total of 310?

zh.jpeg

 

Please help..??

 

 

11 Replies

@Zan_Hanifee 

 

How to insert random (integer) numbers between two numbers without repeats in Excel?

Cclick on header for more infos)

 

This method will apply the RAND function and RANDBETWEEN function to insert random numbers between two numbers into a specified range in Excel. Please view below formulas:

 

Merci pour votre patience et votre temps.

 

Nikolino

Je sais que je ne sais rien (Socrate)

 

Hi @Zan_Hanifee 

If duplicate numbers are acceptable then you can use achieve your goal by using below different formuals:
Paste =RANDBETWEEN(1,310) in A1
Paste =IFERROR(RANDBETWEEN(1,(310-SUM($C$1:C1))),0) in A2 & drag till A9
Paste =IF(SUM(C1:C9)=310,0,310-SUM(C1:C9)) in A10

 

You may also refer the attached file.

 

Thanks

Tauqeer

Thank you for the reply sir
But i want take any number with the help of RANDBETWEEN function and end up with a total of 310?
Hello Sir,
Thank you for giving the solution
I only sent you the formulas for the solution.
As far as I can see, you have received a proposal for a solution from Mr tauqeeracma, which I am very pleased for you.

I hope you continue to enjoy Excel

Nikolino
I know I don't know anything (Socrates)
It helps! Thank you so much sir

@Zan_Hanifee 

A solution using Excel 365.

= LET(
  rand,RANDARRAY(N),
  upper, 1+INT(target*rand/SUM(rand)),
  remainder, MOD(rand,1),
  k, SUM(upper) - target,
  limit, SMALL(remainder,k),
  upper - (remainder<=limit) )

where target =310 and N = 10 for the case described.

Even then, I am not absolutely sure that every possibility has an equal probability of being selected.

image.png

@Zan_Hanifee 

To avoid zero numbers (I guess you mean integers under numbers) and avoid rounding errors we could split the range on 3 parts and calculate as here

image.png

Hello Sir,
Thank you for giving the solution. Thank you so much sir
It worked!!! Thank you so much sir