I have a very simple formula, please see picture.

(black color) Why excel does not randomize the returned values, they are all the same while, in my opinion, they should be different?

How can I tweak my formula, so that the random values returned are different?

I absolutely want to use a dynamic array formula.

If I remove the LET function (red color), which I thought might be the cause of my problem, the problem persists.

CodePudding user response：

That is because RAND() is run first and that value is returned when the if is false. It does not return an array.

That is why they invented RANDARRAY()

=LET(a;B2:F2;IF(a=1;1;RANDARRAY(ROWS(a);COLUMNS(a))))

Another option is SCAN()

=SCAN(0;B2:F2;LAMBDA(a;b;IF(b=1;1;RAND())))

Both of these options will work with a two dimensional range.

CodePudding user response：

You could also use BYCOL:

=BYCOL(B2:F2;LAMBDA(d;IF(d=1;d;RAND())))

CodePudding user response：

Found both of these functions works as well, MAP() & REDUCE()

---

• Formula used in cell B7

=MAP(B2:F2,LAMBDA(k,IF(k=1,k,RAND())))

---

Or, bit verbose with REDUCE()

• Formula used in cell B8

=TOROW(DROP(REDUCE(0,B2:F2,LAMBDA(x,y,VSTACK(x,IF(y=1,y,RAND())))),1),3)

---

Even when RAND() is not ran first, it doesn't return the array,

---

It is to be noted that in the documentation of RANDARRAY() function in

---