0

How to construct a random size, random integer array with (exact) target sum value with a single Excel function (or 'best' viable/working solution given the 'known' volatile issue with this sort of construct)?

Must haves:

  • Single Excel fn
  • No helper function
  • No bespoke VBA, Lambda named ranges, explicit/manual solver method
  • Constraints: sum to exact target value (given), lower / upper dimension (array size given)

Example parameters:

  • random size of between 5-30 (random) integers
  • target sum, say: 1000

Similar Qs/shortcomings:

  • Here - very similar question but not random size array, and summation does not always hit the target mark (sometimes overshoots/undershoots by 1-2 integers)
  • Other solutions to this/related rely upon helper functions or reference the Microsoft 'known bug' issue relating to volatile nature e.g. sequence(randbetween())

In play:

  • Whilst no direct resolve for the intermittent #spill! errors appears to exist, approaches of the form: iferror(sequence(100,,0,0)+volatile,"") would be acceptable
  • I have a draft soln that hits the target sum (exactly, each time - I think!) whenever entire sheet calculated (since f2 + enter otherwise avoids the #spill!), but hoping for a more succinct independently derived solution, in first instance
Improve this question

3 Answers 3

Reset to default
2

I'm not sure if this qualifies as "in-play", but consider the following workaround:

= LAMBDA(target_value,min_sample_size,max_sample_size,
    LET(
        a, RANDARRAY(max_sample_size),
        b, SEQUENCE(max_sample_size) <= RANDBETWEEN(min_sample_size, max_sample_size),
        x, a * b,
        i, ROUND(x * target_value / SUM(x), 0),
        v, target_value - SUM(i),
        IF(b, IF(v, SORTBY(EXPAND(SEQUENCE(ABS(v),, SIGN(v), 0), max_sample_size,, 0), b, -1, a, 1) + i, i), "")
    )
)(1000,5,30)

The spill range size is fixed (max_sample_size), but only the randomly selected sample size is used, and the remaining elements are filled with "".

If there is a variance due to rounding, an additional 1 is randomly allocated to an appropriate number of values (e.g. if v = 3, then 3 random values are increased by 1; if v = -2, then 2 random values are decreased by 1).

Also, if you don't wish to write this as a custom function, simply define the function arguments as variables within LET and remove LAMBDA.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Looks good - but trying to see how the random assignment of surplus / deficit operates -- appears to be sequentially added no? i.e. with expand (sequence (...)
No, it's not sequentially added. It's randomized by SORTBY. Let's say the max sample size is 30, the selected sample size is 20, and the variance (v) is 3. SEQUENCE will return a 3x1 vector of 1's. EXPAND will increase this to 30 rows, with the additional 27 items filled with 0's. This array is then randomized by sorting it: first by the Boolean array (b) to ensure the 1's remain in scope of the selected sample size (top 20 items); then by the original randomized array (a). The results (0's and 1's in random order) are then added to the integer array (i), which remains unsorted.
but wouldn't it be case that smaller items are allocated difference more often that larger ones? or I'm missing something ? vs the approach I took which genuinley randomizes over the sequence in question then sorts so that small/large have roughly same change of either subtraction/addition to clear balance if you know what I mean?
It's pretty random as-is, but to alleviate any concerns about the randomness being tied to the position of higher or lower values in the array, simply change the by_array2 argument of SORTBY from a to RANDARRAY(max_sample_size) (don't recycle the a variable, but rather generate a secondary random array for sorting purposes).
0

The #SPILL! error can be avoided if you accept the result in text form. For example, you can use a formula like this:
=LET(x,RANDARRAY(RANDBETWEEN(5,30)),res,DROP(ROUND(x*1000/SUM(x),0),-1),ARRAYTOTEXT(VSTACK(res,1000-SUM(res))))

Improve this answer

3 Comments

looking for array though... balancing item is nifty - but not exactly 'random' with entirety of res added to final item..
I'm afraid that calculating this with one dynamic formula is not possible in Excel due to the way the worksheet is recalculated. There are two volatile functions that depend on each other – the calculation of the size of the array and the array itself. Their calculation should be made independent. This can be done via VBA. The Rnd function in VBA is not volatile, so you can independently generate the size of the array and the array itself.
see both attempts above - workaround with volatile + non-volatile to mimic behaviour (better so vs. text at least - i.e. these still have spill array (not #spill!) that can be utilized as dependent within other functions etc.)
0

target_ is named range referring to desired sum total.

=LET(t_,target_,w_,RANDBETWEEN(5,30),x_,RANDARRAY(w_,,1,1000000,1),a1_,ROUND(x_*t_/SUM(x_),0),b_,ROWS(a1_),s_,SUM(a1_),y_,REDUCE(a1_,SEQUENCE(1000),LAMBDA(r_,rr_,LET(a_,-2*(s_>t_)+1,x_,a_*(SEQUENCE(b_)=RANDBETWEEN(1,b_)),x1_,r_+(SUM(r_)<>t_)*x_,BYROW(x1_,LAMBDA(a_,MAX(1,a_)))))),IFERROR(SEQUENCE(100,,0,0)+y_,""))

enter image description here

Features:

  • Allocation of surplus/deficit (if any) assigned on random basis across elements s.t. each >=1

  • Volatile + non-volatile (here, sequence(100,,0,0)) 'mimics' desired behaviour (random list size of digits) albeit within a 'static' list range (with residual elements as blanks)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.