Averaging data in a list between two dates.

Hi,
I'm trying to use a table that is here :
http://www.euribor.org/html/download/eonia/eonia%201999-2002.xls
Sheet1 will do for example.

Example

31-déc-02 3.44
27-déc-02 3.03
24-déc-02 3.05
23-déc-02 3.7
20-déc-02 3.5

....

I'm trying to find the average rate (Column B) bewteen two dates (Column A).

I need the average of *all the rates* between two dates (and not just the
average of the rates at the two extremes).

On Sheet1, the average of the five rates between 31/12/2002 and 20/12/2002
is 3.34
Whereas the average of 3.44 (at date 31/12/2002) and 3.5 (at date
20/12/2002) is 3.395.

So, in other words,
(Can't get much more precise than this ..)
I'm loking for a way, when I have the two limit dates, to get the average of
all the intermediary rates into a cell.

Any help appreciated.
TIA.

Grth

Ragdyer
Sat Oct 11 10:15:44 CDT 2008

One way is an *array* formula such as this:

=Average(If((A1:A50>=C1)*(A1:A50<=C2),B1:B50))

With the start date in C1 and the end date in C2.

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Grth" <Grth@GrthGrthGrth.Grth> wrote in message
news:48f0bfe5$0$14076$426a74cc@news.free.fr...
> Hi,
> I'm trying to use a table that is here :
> http://www.euribor.org/html/download/eonia/eonia%201999-2002.xls
> Sheet1 will do for example.
>
> Example
>
> 31-déc-02 3.44
> 27-déc-02 3.03
> 24-déc-02 3.05
> 23-déc-02 3.7
> 20-déc-02 3.5
>
> ....
>
> I'm trying to find the average rate (Column B) bewteen two dates (Column
A).
>
> I need the average of *all the rates* between two dates (and not just the
> average of the rates at the two extremes).
>
> On Sheet1, the average of the five rates between 31/12/2002 and 20/12/2002
> is 3.34
> Whereas the average of 3.44 (at date 31/12/2002) and 3.5 (at date
> 20/12/2002) is 3.395.
>
> So, in other words,
> (Can't get much more precise than this ..)
> I'm loking for a way, when I have the two limit dates, to get the average
of
> all the intermediary rates into a cell.
>
> Any help appreciated.
> TIA.
>
> Grth
>
>
>

Bernard
Sat Oct 11 10:18:13 CDT 2008

With 20/12/2002 in D1 and 30/12/2002 in D2, use
=SUMPRODUCT(--(A1:A1024>=D1),--(A1:A1024<=D2),B1:B1024)/SUMPRODUCT(--(A1:A1024>=D1),--(A1:A1024<=D2))

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.htm

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Grth" <Grth@GrthGrthGrth.Grth> wrote in message
news:48f0bfe5$0$14076$426a74cc@news.free.fr...
> Hi,
> I'm trying to use a table that is here :
> http://www.euribor.org/html/download/eonia/eonia%201999-2002.xls
> Sheet1 will do for example.
>
> Example
>
> 31-déc-02 3.44
> 27-déc-02 3.03
> 24-déc-02 3.05
> 23-déc-02 3.7
> 20-déc-02 3.5
>
> ....
>
> I'm trying to find the average rate (Column B) bewteen two dates (Column
> A).
>
> I need the average of *all the rates* between two dates (and not just the
> average of the rates at the two extremes).
>
> On Sheet1, the average of the five rates between 31/12/2002 and 20/12/2002
> is 3.34
> Whereas the average of 3.44 (at date 31/12/2002) and 3.5 (at date
> 20/12/2002) is 3.395.
>
> So, in other words,
> (Can't get much more precise than this ..)
> I'm loking for a way, when I have the two limit dates, to get the average
> of all the intermediary rates into a cell.
>
> Any help appreciated.
> TIA.
>
> Grth
>
>
>

Ron
Sat Oct 11 12:03:47 CDT 2008

On Sat, 11 Oct 2008 17:01:55 +0200, "Grth" <Grth@GrthGrthGrth.Grth> wrote:

>Hi,
>I'm trying to use a table that is here :
>http://www.euribor.org/html/download/eonia/eonia%201999-2002.xls
>Sheet1 will do for example.
>
>Example
>
> 31-déc-02 3.44
> 27-déc-02 3.03
> 24-déc-02 3.05
> 23-déc-02 3.7
> 20-déc-02 3.5
>
>....
>
>I'm trying to find the average rate (Column B) bewteen two dates (Column A).
>
>I need the average of *all the rates* between two dates (and not just the
>average of the rates at the two extremes).
>
>On Sheet1, the average of the five rates between 31/12/2002 and 20/12/2002
>is 3.34
>Whereas the average of 3.44 (at date 31/12/2002) and 3.5 (at date
>20/12/2002) is 3.395.
>
>So, in other words,
>(Can't get much more precise than this ..)
>I'm loking for a way, when I have the two limit dates, to get the average of
>all the intermediary rates into a cell.
>
>Any help appreciated.
>TIA.
>
>Grth
>
>

If you are using Excel 2007, you could use:

=AVERAGEIFS(values,dates,">="&StartDt,dates,"<="&EndDt)

In earlier versions:

=(SUMIF(Dates,">="&StartDt,Values)-SUMIF(Dates,">"&EndDt,Values))/
(COUNTIF(Dates,">="&StartDt)-COUNTIF(Dates,">"&EndDt))


--ron

Grth
Sat Oct 11 14:08:36 CDT 2008

I'm on XL2007.
Could I combine that into VLOOKUP ?


and ... How do I VLOOKUP a date?

TIA
Grth




"Ron Rosenfeld" <ronrosenfeld@nospam.org> a écrit dans le message de news:
trm1f4hqtl8f8aj16gc5vdi9craa3u3ks2@4ax.com...
> On Sat, 11 Oct 2008 17:01:55 +0200, "Grth" <Grth@GrthGrthGrth.Grth> wrote:
>
>>Hi,
>>I'm trying to use a table that is here :
>>http://www.euribor.org/html/download/eonia/eonia%201999-2002.xls
>>Sheet1 will do for example.
>>
>>Example
>>
>> 31-déc-02 3.44
>> 27-déc-02 3.03
>> 24-déc-02 3.05
>> 23-déc-02 3.7
>> 20-déc-02 3.5
>>
>>....
>>
>>I'm trying to find the average rate (Column B) bewteen two dates (Column
>>A).
>>
>>I need the average of *all the rates* between two dates (and not just the
>>average of the rates at the two extremes).
>>
>>On Sheet1, the average of the five rates between 31/12/2002 and 20/12/2002
>>is 3.34
>>Whereas the average of 3.44 (at date 31/12/2002) and 3.5 (at date
>>20/12/2002) is 3.395.
>>
>>So, in other words,
>>(Can't get much more precise than this ..)
>>I'm loking for a way, when I have the two limit dates, to get the average
>>of
>>all the intermediary rates into a cell.
>>
>>Any help appreciated.
>>TIA.
>>
>>Grth
>>
>>
>
> If you are using Excel 2007, you could use:
>
> =AVERAGEIFS(values,dates,">="&StartDt,dates,"<="&EndDt)
>
> In earlier versions:
>
> =(SUMIF(Dates,">="&StartDt,Values)-SUMIF(Dates,">"&EndDt,Values))/
> (COUNTIF(Dates,">="&StartDt)-COUNTIF(Dates,">"&EndDt))
>
>
> --ron

Ron
Sat Oct 11 18:23:58 CDT 2008

On Sat, 11 Oct 2008 21:08:36 +0200, "Grth" <Grth@GrthGrthGrth.Grth> wrote:

>I'm on XL2007.
>Could I combine that into VLOOKUP ?

Yes, but why?

>
>
>and ... How do I VLOOKUP a date?

I don't understand your question.

But dates are not treated any differently by the VLOOKUP function than they are
treated by any other Excel function.

If you mean to use a date as the Lookup_value, you can either enter a cell
reference where the cell contains the date, or enter a value which will be
interpreted as a date into the function.

It's probably better to use a cell reference. If you enter a date as the
Lookup_value, you need to be sure it is unambiguous.

For example

DATE(2001,12,1) is unambiguous
DATEVALUE("12-JAN-2001") is also unambiguous

But 12/1/2001, depending on your country settings, could be either 1-Dec-2001
or 12-Jan-2001. So you would not want to use:

DATEVALUE("12/1/2001")

--ron

Grth
Sun Oct 12 04:31:18 CDT 2008


I'm having some trouble understanding the AVERAGEIFS function.
It's new to me.
But I'm supposed to be making Sunday lunch now so I'll get my head around it
later ...I hope.

Thanks for your help, Ron.


"Ron Rosenfeld" <ronrosenfeld@nospam.org> a écrit dans le message de news:
cnc2f4l0mj4gt9hmbnh25t7a6p1jnpblsj@4ax.com...
> On Sat, 11 Oct 2008 21:08:36 +0200, "Grth" <Grth@GrthGrthGrth.Grth> wrote:
>
>>I'm on XL2007.
>>Could I combine that into VLOOKUP ?
>
> Yes, but why?
>
>>
>>
>>and ... How do I VLOOKUP a date?
>
> I don't understand your question.
>
> But dates are not treated any differently by the VLOOKUP function than
> they are
> treated by any other Excel function.
>
> If you mean to use a date as the Lookup_value, you can either enter a cell
> reference where the cell contains the date, or enter a value which will be
> interpreted as a date into the function.
>
> It's probably better to use a cell reference. If you enter a date as the
> Lookup_value, you need to be sure it is unambiguous.
>
> For example
>
> DATE(2001,12,1) is unambiguous
> DATEVALUE("12-JAN-2001") is also unambiguous
>
> But 12/1/2001, depending on your country settings, could be either
> 1-Dec-2001
> or 12-Jan-2001. So you would not want to use:
>
> DATEVALUE("12/1/2001")
>
> --ron