Calculate the average using the Lookup function or similar

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

=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" <LarsF@discussions.microsoft.com> wrote in message
news:BD5B7112-496C-4939-9BD7-0848ACE579C4@microsoft.com...
> 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

Works great - thanks!!!

/Lars

"Bob Phillips" wrote:

> =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" <LarsF@discussions.microsoft.com> wrote in message
> news:BD5B7112-496C-4939-9BD7-0848ACE579C4@microsoft.com...
> > 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
>
>
>