Averaging data in a list between two dates.

  • Thread starter Thread starter Grth
  • Start date Start date
G

Grth

Hi,
I'm trying to use a table that is here :
http://www.euribor.org/html/download/eonia/eonia 1999-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
 
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.
 
Hi,
I'm trying to use a table that is here :
http://www.euribor.org/html/download/eonia/eonia 1999-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
Click to expand...

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
 
I'm on XL2007.
Could I combine that into VLOOKUP ?


and ... How do I VLOOKUP a date?

TIA
Grth
 
I'm on XL2007.
Could I combine that into VLOOKUP ?
Click to expand...

Yes, but why?
and ... How do I VLOOKUP a date?
Click to expand...

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
 
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.
 
Back
Top