Convert function to sub

[Craig]

I know this may sound odd, but I was wondering if there
was a user defined function equivalent to the following
array formula:

={AVERAGE(IF((DateRange>=A1)*
(DateRange<=A2),StockPrice,FALSE))}

What this does is look up the dates in 'DateRange' that
fall between the dates entered into cells 'A1' and 'A2',
and then takes the average of the prices from the
range 'StockPrice'.

Thanks for any help.

 

[Frank Kabel]

Hi
why do you want a UDF for this. This would surely been slower?

 

[Craig]

You're right. I guess I'm curious if it's at all possible
to put any type of array formula like this into a module -
but I see your point - why bother.

Thanks

 

[Rob van Gelder]

FYI it's possible to convert it into a User Defined Formula.
If you're still interested, let us know for examples.

 

[Bob Phillips]

In answer to your curiosity, you have two choices.

The brute-force approach would be to iterate through all of the cells in
DateRange and check for being in the range, and average the corresponding
values in StockPrice.

The second would be to call the worksheetfunction from within the VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)