Can an array formula be used in a user defined function?

Here's what I need to do:

I have a rectangular array with values for a number of variables
(columns) in a number of samples (rows).

In another (square) array, I want to generate the variance of the
natural log of the ratios of all possible pairs of variables. Thus,
cell mn of the output array has the variance of the logs of the ratios
of every pair of values in row m and row n of the input array. I can
do this for an individual cell of the output array with an array
formula, but as there are about 35 columns of variables there would be
over a thousand array formulas to type in!. I can't use Copy and
Paste, because of the way column n of the input becomes row n of the
output.

I could probably do it in a user defined function, but I'm not sure if
I can use an array formula in a udf, and someone may be able to think
up a more efficient way of doing what I want.

Any ideas?

SS.


---
Message posted from http://www.ExcelForum.com/

You can use an excel array formula in a UDF if you use Application.Evaluate
or Worksheet.Evaluate to evaluate it.

It may well be faster to write a UDF that reads the rectangular range into
an array and then calculates the square output array and returns it when the
UDF is entered as an array formula.
The only problem would be that Excel limits you to returning a max of 5461
elements with an array formula UDF prior to Excel 2002 (well unless you want
to start making registry changes for Excel 2000).

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"stratasmith " wrote in message
...
Here's what I need to do:

I have a rectangular array with values for a number of variables
(columns) in a number of samples (rows).

In another (square) array, I want to generate the variance of the
natural log of the ratios of all possible pairs of variables. Thus,
cell mn of the output array has the variance of the logs of the ratios
of every pair of values in row m and row n of the input array. I can
do this for an individual cell of the output array with an array
formula, but as there are about 35 columns of variables there would be
over a thousand array formulas to type in!. I can't use Copy and
Paste, because of the way column n of the input becomes row n of the
output.

I could probably do it in a user defined function, but I'm not sure if
I can use an array formula in a udf, and someone may be able to think
up a more efficient way of doing what I want.

Any ideas?

SS.


---
Message posted from http://www.ExcelForum.com/

I wasn't familiar with application.evaluate/worksheet.evaluate but I'll
look into them.

It's also occurred to me that I could do the whole thing with a
subroutine, looping through the cells of the output array (or at least,
those above the leading diagonal, as it's going to be mirrored in the
other half), but this might run up against the same limitation that you
mentioned. (I'm using Excel 97, by the way.)

Any other thoughts?

SS.


---
Message posted from http://www.ExcelForum.com/

A Subroutine does not have the same 5461 limits as a worksheet function.

If you can use it as a sub attached to a button or something then I would
recommend using a sub.
VBA is surprisingly fast at handling arrays and arithmetic so this might be
the right approach.

Just make sure you read the input range in a single statement into a single
variant variable (not an array of variants) and process the results into a
square array of doubles:
something like this

Dim vArrIn as variant
dim j as long
dim k as long
dim dAnsa(100,100) as double
'
' setup excel environment
'
application.calculation=xlmanual
application.screenupdating=false
'
' get the input data
'
vArrIn =worksheets("sheet1").range("A1:IV10000")
'
' process input data
'
for j=1 to 10000
for k=1 to 256

...... =vArrIn(j,k)

dAnsa(jj,kk)= ...

next k
next j
'
' put results back to worksheet
'
worksheets("sheet2").range("z5").resize(100,100)=d Ansa
'
' restore excel environment
'
application.calculation=xlautomatic
application.screenupdating=true

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"stratasmith " wrote in message
...
I wasn't familiar with application.evaluate/worksheet.evaluate but I'll
look into them.

It's also occurred to me that I could do the whole thing with a
subroutine, looping through the cells of the output array (or at least,
those above the leading diagonal, as it's going to be mirrored in the
other half), but this might run up against the same limitation that you
mentioned. (I'm using Excel 97, by the way.)

Any other thoughts?

SS.


---
Message posted from http://www.ExcelForum.com/