[SOLVED] Dynamic annual average

Hi, I keep a rough weekly diary on the development of a certain numeric
value. Now I've got a series of data entries in a chart as follows:

A1 - date of entry, eg. "23rd July 2006"
A2 - reading data value, eg "100"

I want to create a dynamic average on line A3 which tells me the
average of data entries during the past year. Problem is, I've read the
data value in irregular intervals and not every day (last year for
example on 22nd July, the year before 10th July etc). My Excel
understands the dates entered and draws a neat proportioned chart on
development of data values for the whole period of surveillance, but
I'd need to be able to see the effect of the latest reading on the
dynamic average of the past year only. Is there a smart way of doing
this?

(the way I don't want to do it: create a column for every single day in
calendar and always enter my data value in that column... that way I
could easily subtract the value of 365 days ago from present value, but
in 10 years I get a monster of a chart. There must be a better way!)

1. When you get past 256 entries, you'll run out of columns, so you really
ought to use column A for dates, column B for values, and column C for the
calculated averages.

2. I made an example with dates in column A, starting in A4 (leaving some
room for headers and other information at the top). I put values in column
B, starting in B4. I array-entered the following formula in C4, then copied
it down as far as I had dates and values in columns A and B. To array enter
a formula, type or paste the formula, then hold Ctrl+Shift while pressing
Enter. If done correctly, Excel places the formula within {curly braces}.

=SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4)))*($B$4:$B4))/SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4))))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


<[email protected]> wrote in message
news:[email protected]...
> Hi, I keep a rough weekly diary on the development of a certain numeric
> value. Now I've got a series of data entries in a chart as follows:
>
> A1 - date of entry, eg. "23rd July 2006"
> A2 - reading data value, eg "100"
>
> I want to create a dynamic average on line A3 which tells me the
> average of data entries during the past year. Problem is, I've read the
> data value in irregular intervals and not every day (last year for
> example on 22nd July, the year before 10th July etc). My Excel
> understands the dates entered and draws a neat proportioned chart on
> development of data values for the whole period of surveillance, but
> I'd need to be able to see the effect of the latest reading on the
> dynamic average of the past year only. Is there a smart way of doing
> this?
>
> (the way I don't want to do it: create a column for every single day in
> calendar and always enter my data value in that column... that way I
> could easily subtract the value of 365 days ago from present value, but
> in 10 years I get a monster of a chart. There must be a better way!)
>

Hi Jon,

thanks for your advice. That with using a column for values should have
been obvious, of course.

I seem to have a problem inserting your formula: Excel gets stuck in
the '...1,MONTH...' part and claims that there's an error in formula.
When I try writing '+' instead of' commas, Excel claims that I've
entered too few arguments (whatever that means).

I spent some hours trying to work out what your formula actually does
(I'm not sure yet) in order to understand the possible error. Now I'm
in the conclusion that I don't get the DATE(YEAR(n)-1,MONTH(n),DAY(n))
- part to work properly - at least not in my Excel 2000: the result is
not a date a year before.

What could be wrong? Can you give me a hint?

AS


Jon Peltier wrote:
> 1. When you get past 256 entries, you'll run out of columns, so you really
> ought to use column A for dates, column B for values, and column C for the
> calculated averages.
>
> 2. I made an example with dates in column A, starting in A4 (leaving some
> room for headers and other information at the top). I put values in column
> B, starting in B4. I array-entered the following formula in C4, then copied
> it down as far as I had dates and values in columns A and B. To array enter
> a formula, type or paste the formula, then hold Ctrl+Shift while pressing
> Enter. If done correctly, Excel places the formula within {curly braces}.
>
> =SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4)))*($B$4:$B4))/SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4))))
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>

Try this. In a blank cell, type =DATE(2006,7,25) and press Enter. The cell
should now show the date. This is only part of the formula, of course.

I can think of two sources of error:

1. Your dates are not in a recognizable format ("23rd July 2006"), so Excel
may be assuming a numerical value of zero, thinking that you've entered
text. YEAR(0)-1 will then produce an error. Use "23 July 2006" instead.

2. When you put my formula into the appropriate cell, you don't enter it
using the Enter key, you do so by holding down Shift+Ctrl, them pressing the
Enter key.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


<[email protected]> wrote in message
news:[email protected]...
> Hi Jon,
>
> thanks for your advice. That with using a column for values should have
> been obvious, of course.
>
> I seem to have a problem inserting your formula: Excel gets stuck in
> the '...1,MONTH...' part and claims that there's an error in formula.
> When I try writing '+' instead of' commas, Excel claims that I've
> entered too few arguments (whatever that means).
>
> I spent some hours trying to work out what your formula actually does
> (I'm not sure yet) in order to understand the possible error. Now I'm
> in the conclusion that I don't get the DATE(YEAR(n)-1,MONTH(n),DAY(n))
> - part to work properly - at least not in my Excel 2000: the result is
> not a date a year before.
>
> What could be wrong? Can you give me a hint?
>
> AS
>
>
> Jon Peltier wrote:
>> 1. When you get past 256 entries, you'll run out of columns, so you
>> really
>> ought to use column A for dates, column B for values, and column C for
>> the
>> calculated averages.
>>
>> 2. I made an example with dates in column A, starting in A4 (leaving some
>> room for headers and other information at the top). I put values in
>> column
>> B, starting in B4. I array-entered the following formula in C4, then
>> copied
>> it down as far as I had dates and values in columns A and B. To array
>> enter
>> a formula, type or paste the formula, then hold Ctrl+Shift while pressing
>> Enter. If done correctly, Excel places the formula within {curly braces}.
>>
>> =SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4)))*($B$4:$B4))/SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4))))
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>

Hi Jon,

I got something working, thanks a million for your advice! For some
reason I have to separate year, month and day by using semicolon, not
comma as you suggest (ie '=DATE(2006;7;25)') but anyway the function
works now.

The result is not correct yet though: the figure I get for annual
average should not be rising but it should stay on about same level, so
something's not working correctly. I'm working on it (every now and
then; I've got no deadline for this).

Anssi