an array formula can be used only by functions that accept arrays.

In this application, yes, that's correct.

And some array formulas involve functions that return an array result.
Isn't that what's happening with FREQUENCY(A1:A10,B1:B10)?

Yes, that's correct. ROW() is another function that returns an array. Even
if it's simply ROW(A1) it's still a single element array.

The difference was that I passed a array to RANDBETWEEN,
whereas yousimply called RAND().

Yes, but as I noted, RANDBETWEEN won't take arrays as arguments so it
operates on the first element of the array.

You seemed to be expecting RAND()*ROW(A1:A3) to
translate into {RAND()*1;RAND()*2;RAND()*3}.

No, I expected RAND()*{1;2;3}.


--
Biff
Microsoft Excel MVP


"joeu2004" wrote in message
...
On Jun 14, 8:37 pm, "T. Valko" wrote:
ROW(...) is generating an array *but* RANDBETWEEN won't
accept the array and only takes the first element of the
array for the argument.

Oh, I think I understand. I thought array formulas caused Excel to
iterate over the array. That is, {RANDBETWEEN(0,ROW(A1:A4))} would
turn into
{RANDBETWEEN(0,1);RANDBETWEEN(0,2);RANDBETWEEN(0,3 );RANDBETWEEN(0,4)}.
But you seem to be saying it translates into RANDBETWEEN(0,
{1;2;3;4}). Ergo, an array formula can be used only by functions that
accept arrays. Makes sense.

And some array formulas involve functions that return an array
result. Isn't that what's happening with FREQUENCY(A1:A10,B1:B10)?
That seems to be what you are alluding to below. But that was never
my expectation in my RANDBETWEEN usage.


As Harlan noted in his reply to me, RAND, and in this
case RANDBETWEEN, don't return an array.

The difference was that I passed a array to RANDBETWEEN, whereas you
simply called RAND(). You seemed to be expecting RAND()*ROW(A1:A3) to
translate into {RAND()*1;RAND()*2;RAND()*3}. I would have expected
RAND()*{1;2;3}. So Harlan's comment was no surprise to me: RAND() is
computed only once.

Oh well, water under the bridge. Thanks for the explanation. It
should help me in the future.