How to refer to a large quantity of numbers using an array?

  • Thread starter Thread starter Ying
  • Start date Start date
Y

Ying

Say if I want to use an array to refer to numbers 1 to 200
without displaying them on a worksheet, how do I do it?

Thanks!
 
Say if I want to use an array to refer to numbers 1 to 200
without displaying them on a worksheet, how do I do it?
Click to expand...

ROW(INDIRECT("1:200")) is most robust. To make that a horizontal array, wrap it
inside TRANSPOSE(.).
 
What if they are 200 "random" numbers?
-----Original Message-----
...

ROW(INDIRECT("1:200")) is most robust. To make that a horizontal array, wrap it
inside TRANSPOSE(.).

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
.
Click to expand...
 
What if they are 200 "random" numbers?
Click to expand...
...

It always helps to give full details the first time round.

What do you mean by 'random'? Merely arbitrary, like unsorted birthdates for 200
people? If so, you won't be able to squeeze that much data into an array
constant referred to by a defined name. That sort of data should be stored in
cells, if not out of strict necessity then for expedience.

On the other hand, large samples of pseudorandom numbers present other problems
because Excel's RAND() function won't generate arrays. While the defined name X
referring to =ROW(INDIRECT("1:200"))*0+RAND() would behave like a sample of 200
instances of a random variable if entered in an array formuls for a range
spanning 200 rows, it'll behave as 200 instances of the same value if entered in
single cell formulas like =DEVSQ(X), which returns zero or very close to it.

You could try Y referring to =MOD(ROW(INDIRECT("1:200"))/RAND(),1) which isn't
much worse than 200 separate calls to Excel's RAND(), and DEVSQ(Y) will give
values much closer to what a uniform RV should give. However, it may still have
problems used in other formulas.

What are you trying to do with these random numbers?
 
Actually, what I'm trying to do is calculate an inverse
matrix of a matrix without displaying the matrix on a
worksheet. To do that, I need to define ranges for each
row (or column) of the matrix (assume I can calculate the
elements beforehand). It seems pretty easy mathematically:
you could say, X = (x1, x2, ..., xi, ..., xn), where xi =
such and such, but how do you translate that into VBA
codes?

Thanks for your help.

Ying
-----Original Message-----
...
...

It always helps to give full details the first time round.

What do you mean by 'random'? Merely arbitrary, like unsorted birthdates for 200
people? If so, you won't be able to squeeze that much data into an array
constant referred to by a defined name. That sort of data should be stored in
cells, if not out of strict necessity then for expedience.

On the other hand, large samples of pseudorandom numbers present other problems
because Excel's RAND() function won't generate arrays. While the defined name X
referring to =ROW(INDIRECT("1:200"))*0+RAND() would behave like a sample of 200
instances of a random variable if entered in an array formuls for a range
spanning 200 rows, it'll behave as 200 instances of the same value if entered in
single cell formulas like =DEVSQ(X), which returns zero or very close to it.

You could try Y referring to =MOD(ROW(INDIRECT
Click to expand...
("1:200"))/RAND(),1) which isn't
 
Actually, what I'm trying to do is calculate an inverse
matrix of a matrix without displaying the matrix on a
worksheet. To do that, I need to define ranges for each
row (or column) of the matrix (assume I can calculate the
elements beforehand). It seems pretty easy mathematically:
you could say, X = (x1, x2, ..., xi, ..., xn), where xi =
such and such, but how do you translate that into VBA
codes?
Click to expand...

If you have rows of some *SMALL* matrix stored in defined names like X1, X2, X3,
X4, then you could build the matrix as the defined name M referring to

=X_1*{1;0;0;0}+X_2*{0;1;0;0}+X_3*{0;0;1;0}+X_4*{0;0;0;1}

However, at 200+ entries per row, you can't stuff even the rows into defined
names. You could write a VBA user-defined function to return the matrix,
something like

Function foo() As Variant
Dim rv(1 To 200, 1 To 200) As Double
rv(1, 1) = 123
':
rv(200, 200) = 987
foo = rv
End Function

then call from Excel as =MINVERSE(foo()).

Now to the deeper issue, Excel may be better than other spreadsheets when it
comes to inverting matrices, but it's still not as good as dedicated math and
stats software. If your matrices are huge (>256 entries per column/row), Excel
ain't too useful. Are you trying to invert the matrix manually (some form of
Gauss-Jourdan) rather than using Excel's MINVERSE function? If so, why?
 
Back
Top