arraysexcelrandom

Generate Single Column Random Number Array based on Cell Value

I have a working formula that I found in the internet {credit to owner} that needs a little tweaking to suit my need. The formula is currently working to provide random numbers based on the following requirements

  1. Generate n random numbers based on nearest 1,000th of Cell Value ex.$N$4 = 15,xxx.xx = 15 numbers

  2. Total sum of generated random numbers should be equal to Cell Value = ex.$N$4 = 15,xxx.xx

  3. Single column sorted lowest to highest

  4. 2 decimal places

  5. Generated Value should be minimum 1,000.xx +

    =SORT(LET(x_,UNIQUE(RANDARRAY((N4/1000),1,1000,1002,FALSE)),y_,ROUND(x_*N4/SUM(x_),2),w_,N4-SUM(y_),z_,VSTACK(DROP(y_,-1),TAKE(y_,-1)+w_),z_))

The current formula works well except that I need unique numbers. Sometimes it generates 2-6 identical numbers depending on cell value, I only want unique numbers with above conditions.

I think the generated randoms numbers are truly unique but the problem occurs when its rounded to 2 decimal places that's why there will be identical numbers. How can I avoid this and improve on the formula.

See sample result of the formula with identical numbers generated for reference.

enter image description here

Thank You!

Solution

  • Here is solution with the 100% guarantee:

    =LET(lam,LAMBDA(arg,SORT(LET(arr,SORT(arg),res,MAP(DROP(arr,1),DROP(arr,-1),LAMBDA(x,y,IF(x=y,x-0.01,x))),VSTACK(SUM(arr)-SUM(res),res)))),
  ram,LAMBDA(arr,fn,LET(car,lam(arr),IF(ROWS(car)>ROWS(UNIQUE(car)),fn(car,fn),car))),
  ram(LET(x,A1,y,0.1+RANDARRAY(x/1000),w,DROP(ROUND(y*x/SUM(y),2),1),VSTACK(x-SUM(w),w)),ram))

    enter image description here

    The "lam" function resolves the array for two equal values.

    The "ram" function resolves the array repeatedly applying lam until no equal values.

    The input is in the third line - A1.

    P.S.

    You can use the basis to generate numbers above it:

    =LET(lam,LAMBDA(arg,SORT(LET(arr,SORT(arg),res,MAP(DROP(arr,1),DROP(arr,-1),LAMBDA(x,y,IF(x=y,x-0.01,x))),VSTACK(SUM(arr)-SUM(res),res)))),
  ram,LAMBDA(arr,fn,LET(car,lam(arr),IF(ROWS(car)>ROWS(UNIQUE(car)),fn(car,fn),car))),
  ram(LET(x,A1,basis,1000,n,INT(x/1000),y,RANDARRAY(n),w,DROP(ROUND(basis+y*(x-basis*n)/SUM(y),2),1),VSTACK(x-SUM(w),w)),ram))