Convert function to sub
 

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.

Convert function to sub
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Craig" schrieb im Newsbeitrag
...
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.

Convert function to sub
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Craig" schrieb im
Newsbeitrag
...
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.


.

Convert function to sub
 

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Craig" wrote in message
...
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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Craig" schrieb im
Newsbeitrag
...
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.


.

Convert function to sub
 

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)

"Craig" wrote in message
...
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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Craig" schrieb im
Newsbeitrag
...
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.


.