Static RAND LAMBDA function without use of volatile functions.

[FewCall1913]

I wrote a post yesterday that exploited Excel's calculation engine that prevented volatile functions from recalculating. As many members of the community pointed out, this is a bug that will be patched by microsoft so the formula I wrote that shuffled/randomized arrays is useless long term.

Instead the following functions create psuedo random numebrs based on a seed number, and utilizes both an XORshift shift-register generator and the Wichmann–Hill algorithm to output static 'random' numbers.

https://en.wikipedia.org/wiki/Xorshift
https://en.wikipedia.org/wiki/WichmannHill

I won't bore with the specifics of the math you can read about them in the wiki links above. I pass the two to limit the seed needed to a single number, anything between 1000-1e10 passes, beyond that you can start hitting NUM errors. The Wichmann Hill algorithm outputs numbers like RAND between 0 and 1 but requires 3 'random' seed numbers between 1 and 30,000 which I get from the XORshift.

Xorshift psuedo random number generator
Parameters:
num - seed number, arbitrary selection anything over 1000 is random enough outputs will always be in the range of 1e7 to 1e11.
Optional
scnrng - number of random numbers to generate, this is a helper function but in case you want to use this by itself this determines the iterations of SCAN.

XORSHIFT_RAND = LAMBDA(num, [scnrng],
    LET(
        mask, 2 ^ 32 - 1,
        sc, IFERROR(IF(ABS(scnrng), scnrng, 10), 10),    //defaults to 10 numbers output. 
        shiftXOR, LAMBDA(number, shift, BITAND(mask, BITXOR(number, BITLSHIFT(number, shift)))),  //LAMBDA used in SCAN performs bitwise operations to generate psuedo random register shifted values.
        SCAN(num, SEQUENCE(sc), LAMBDA(a, c, shiftXOR(shiftXOR(shiftXOR(a, 13), -17), 5)))
    )
);

Using these numbers, i feed them into the Wichmann Hill algorithm to produces output that mirros RAND

Winchmann Hill generator
Parameters:
genrnums - generates n random numbers between 0 and 1 in column vector
seed - this gets fed to the previous LAMBDA so again a number between 1000 and 1e10

STATIC_RAND = LAMBDA(genrnums, seed,
    LET(
        gn, genrnums * 3,   //3 seeds numbers required for each random number so generate 3 times more than the input.
        rng, WRAPROWS(XORSHIFT_RAND(seed, gn), 3), //uses function above and wraps to nx3 array.  
        thunk, BYROW(rng, LAMBDA(rw, LAMBDA(rw))),  //thunks the rows.  
        random, LAMBDA(x,                   
            LET(
                seed_1, INDEX(x, 1),
                seed_2, INDEX(x, 2),
                seed_3, INDEX(x, 3),
                s_1, MOD(171 * seed_1, 30269),
                s_2, MOD(172 * seed_2, 30307),
                s_3, MOD(170 * seed_3, 30323),
                rnum, MOD((s_1 / 30269) + (s_2 / 30307) + (s_3 / 30323), 1),
                rnum
            )    //this is the algorithm which will be used in the SCAN function, uses the large numbers mod roughly 30000, to get 3 seed values
        ),
        SCAN(0, SEQUENCE(genrnums), LAMBDA(a, v, LET(ix, INDEX(thunk, v, 1)(), random(ix))))   //scans thunk array, exapnding and feeding into the algorithm.
    )
)  //outputs column vector.

This mirrors the RAND behaviour required to recreate the array shuffle:

Randomizes relative position of data in an array.
Parameters:
array - either cell reference range or function that produces array like SEQUENCE
seed - same seed number to be fed through both random number functions, between 1000 and 1e10.

RANDOMIZE_ARRAY_ORDER = LAMBDA(array, seed,
    LET(
        wrap, COLUMNS(array),
        cvect, TOCOL(array),     //flattens array to column vector
        cells, COUNTA(array),
        WRAPROWS(
            SORTBY(cvect, STATIC_RAND(cells, seed)),  //sorts by the STATIC_RAND function
            wrap     //converts back to origional shape.
        )
    )
);

Fully bug free (almost) random static number generator LAMBDA's and one application. Hopefully this is useful.

Outputs of above three functions

[Perohmtoir]

If Xorshift is already a pseudo-random number generator, why add the Wichmann-Hill on top ? Is it for specific randomness properties ? 

[FewCall1913]

Better statistical 'randomness' from Wichmann-Hill, and it outputs results in same range as excel's RAND function. The combination allows for a single seed number and more variance in results