Calculate the average using the Lookup function or similar

[Lars F]

Hi! I'm using the lookup function to find a specific share price for a
certain date and that works great. However, I would now like to be able to
calculate the average for a certain period, for example a quarter. This would
be approx 65 observations starting at a defined date and ending at another
defined date - everything in columns. My question is now wheter I can qombine
the lookup and average function in order to achieve this? ..or any other way
to do this?

I have tried with
=AVERAGE((LOOKUP(C4,'share price'!$B$3:$B$1007,'share
price'!$C$3:$C$1007)),(LOOKUP(E4,'share price'!$B$3:$B$1007,'share
price'!$C$3:$C$1007)))

But that formula only returns the average of those two numbers found and I
cant put a colon instead of a comma between the "cells looked up" in order to
get the range of numbers - without an error message.

Can anyone give me some help with this one?

Regards,

/Lars

 

[Bob Phillips]

=AVERAGE(IF(('share price'!$B$3:$B$1007>=C4)*('share
price'!$B$3:$B$1007<=E4),'share price'!$C$3:$C$1007))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Lars F]

Works great - thanks!!!

/Lars

=AVERAGE(IF(('share price'!$B$3:$B$1007>=C4)*('share
price'!$B$3:$B$1007<=E4),'share price'!$C$3:$C$1007))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)