rolling 12 month average

I need formula that will determine a rolling 12 month average. I'm starting
with individual days, so I need something that will recognise the start and
end of each month, something that is date based rather than just count based.
Any ideas?
Thanks in advance!
--
gevans

If you want to average for data in col d and dates in col a then try this
array formula which must be entered using ctrl+shift+enter vs just enter.

=AVERAGE(IF(MONTH(ChecksA)=2,ChecksD))

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"gevans" <gevans@discussions.microsoft.com> wrote in message
news:D4088006-1F63-4C39-9588-96B13B554D1A@microsoft.com...
>I need formula that will determine a rolling 12 month average. I'm
>starting
> with individual days, so I need something that will recognise the start
> and
> end of each month, something that is date based rather than just count
> based.
> Any ideas?
> Thanks in advance!
> --
> gevans

Start of month (with date in A1)

=DATE(YEAR(A1),MONTH(A1),1)

end

=DATE(YEAR(A1),MONTH(A1)+1,0)

12 months ago from end of month subtract 12

=DATE(YEAR(A1),MONTH(A1)-11,0)

=AVERAGE(IF((A2:A1000>=DATE(YEAR(A1),MONTH(A1)-11,0))*(A2:A1000<=DATE(YEAR(A1),MONTH(A1)+1,0)),B2:B1000))


will average values in B2:B1000 where dates in A2:A1000 are greater than 12
months from end of month of date in A1 AND smaller than end of month with
date in A1

A1 can be replaced with TODAY() to use today's date

needs to be entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"gevans" <gevans@discussions.microsoft.com> wrote in message
news:D4088006-1F63-4C39-9588-96B13B554D1A@microsoft.com...
>I need formula that will determine a rolling 12 month average. I'm
>starting
> with individual days, so I need something that will recognise the start
> and
> end of each month, something that is date based rather than just count
> based.
> Any ideas?
> Thanks in advance!
> --
> gevans

Thanks, Peo, that will work just fine!
--
gevans


"Peo Sjoblom" wrote:

> Start of month (with date in A1)
>
> =DATE(YEAR(A1),MONTH(A1),1)
>
> end
>
> =DATE(YEAR(A1),MONTH(A1)+1,0)
>
> 12 months ago from end of month subtract 12
>
> =DATE(YEAR(A1),MONTH(A1)-11,0)
>
> =AVERAGE(IF((A2:A1000>=DATE(YEAR(A1),MONTH(A1)-11,0))*(A2:A1000<=DATE(YEAR(A1),MONTH(A1)+1,0)),B2:B1000))
>
>
> will average values in B2:B1000 where dates in A2:A1000 are greater than 12
> months from end of month of date in A1 AND smaller than end of month with
> date in A1
>
> A1 can be replaced with TODAY() to use today's date
>
> needs to be entered with ctrl + shift & enter
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> Portland, Oregon
>
>
>
>
> "gevans" <gevans@discussions.microsoft.com> wrote in message
> news:D4088006-1F63-4C39-9588-96B13B554D1A@microsoft.com...
> >I need formula that will determine a rolling 12 month average. I'm
> >starting
> > with individual days, so I need something that will recognise the start
> > and
> > end of each month, something that is date based rather than just count
> > based.
> > Any ideas?
> > Thanks in advance!
> > --
> > gevans
>
>

Don, I don't understand the function (ChecksA), and I can't get your formula
to work. I am entering it as written, and using Ctr/Shift/Enter so it will
enter as an array.
--
gevans


"Don Guillett" wrote:

> If you want to average for data in col d and dates in col a then try this
> array formula which must be entered using ctrl+shift+enter vs just enter.
>
> =AVERAGE(IF(MONTH(ChecksA)=2,ChecksD))
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "gevans" <gevans@discussions.microsoft.com> wrote in message
> news:D4088006-1F63-4C39-9588-96B13B554D1A@microsoft.com...
> >I need formula that will determine a rolling 12 month average. I'm
> >starting
> > with individual days, so I need something that will recognise the start
> > and
> > end of each month, something that is date based rather than just count
> > based.
> > Any ideas?
> > Thanks in advance!
> > --
> > gevans
>
>
>

checksa and b are defined names of ranges. Just substitute your range
addresses instead.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"gevans" <gevans@discussions.microsoft.com> wrote in message
news:FCF2C3C3-970F-43FF-8B24-060DA85B01A7@microsoft.com...
> Don, I don't understand the function (ChecksA), and I can't get your
> formula
> to work. I am entering it as written, and using Ctr/Shift/Enter so it
> will
> enter as an array.
> --
> gevans
>
>
> "Don Guillett" wrote:
>
>> If you want to average for data in col d and dates in col a then try this
>> array formula which must be entered using ctrl+shift+enter vs just enter.
>>
>> =AVERAGE(IF(MONTH(ChecksA)=2,ChecksD))
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "gevans" <gevans@discussions.microsoft.com> wrote in message
>> news:D4088006-1F63-4C39-9588-96B13B554D1A@microsoft.com...
>> >I need formula that will determine a rolling 12 month average. I'm
>> >starting
>> > with individual days, so I need something that will recognise the start
>> > and
>> > end of each month, something that is date based rather than just count
>> > based.
>> > Any ideas?
>> > Thanks in advance!
>> > --
>> > gevans
>>
>>
>>