Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
gevans
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
 
Posts: n/a
Default rolling 12 month average

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

"gevans" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default rolling 12 month average

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" wrote in message
...
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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
gevans
 
Posts: n/a
Default rolling 12 month average

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" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
gevans
 
Posts: n/a
Default rolling 12 month average

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

"gevans" wrote in message
...
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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rolling up to the correct Month Dilemma salooha Excel Discussion (Misc queries) 0 February 9th 06 10:18 PM
Formula for calculating a rolling 12 month average in excel? Jeff Excel Discussion (Misc queries) 1 December 9th 05 10:11 PM
Rolling 3 mth Average dallin Excel Worksheet Functions 1 November 22nd 05 05:10 PM
Average starting with first month Jim Excel Discussion (Misc queries) 7 October 29th 05 12:48 PM
Calculating Month Average Exch Rate from Exchange Rates Calendar!!! StanUkr Excel Worksheet Functions 0 September 14th 05 11:12 AM


All times are GMT +1. The time now is 05:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"