EXCEL HELP!!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A B
Aug-02 255
Sep-02 255
Oct-02 500
Nov-02 365
Dec-02 200
Jan-03 100
Feb-03 200
Mar-03 300
Apr-03 200
May-03 100
Jun-03 100
Jul-03 200
Aug-03 500
Sep-03 700
Oct-03
Nov-03
Dec-03
Jan-04
Rolling 12 Months?

What I am looking to do is creat a formula that will add up the last 12 months of data entered. So in this example it would sum up Sept 03- Oct 02. And when I enter in Oct 03, i want it to automaticly sum Nov 02 through Oct 03, and so on.
Also, it is important to know that all the cells in COL.B have sumformuals in them, so they show "0
ANY IDEAS?
 
try something like this where a1 contains the last month formatted date
=sumproduct((month(a2:a200>=month(a1)-12)*(month(a2:a200=month(a1))*b2:b200)

--
Don Guillett
SalesAid Software
(e-mail address removed)
MattB said:
A B
Aug-02 255
Sep-02 255
Oct-02 500
Nov-02 365
Dec-02 200
Jan-03 100
Feb-03 200
Mar-03 300
Apr-03 200
May-03 100
Jun-03 100
Jul-03 200
Aug-03 500
Sep-03 700
Oct-03
Nov-03
Dec-03
Jan-04
Rolling 12 Months?

What I am looking to do is creat a formula that will add up the last 12
Click to expand...
months of data entered. So in this example it would sum up Sept 03- Oct 02.
And when I enter in Oct 03, i want it to automaticly sum Nov 02 through Oct
03, and so on.
 
A B
Aug-02 255
Sep-02 255
Oct-02 500
Nov-02 365
Dec-02 200
Jan-03 100
Feb-03 200
Mar-03 300
Apr-03 200
May-03 100
Jun-03 100
Jul-03 200
Aug-03 500
Sep-03 700
Oct-03
Nov-03
Dec-03
Jan-04
Rolling 12 Months?

What I am looking to do is creat a formula that will add up the last 12 months of data entered. So in this example it would sum up Sept 03- Oct 02. And when I enter in Oct 03, i want it to automaticly sum Nov 02 through Oct 03, and so on.
Also, it is important to know that all the cells in COL.B have sumformuals in them, so they show "0"
ANY IDEAS?
Click to expand...

*Array-Enter* =SUM(OFFSET(DataRng,MAX((DataRng<>0)*ROW(DataRng))-1,0,-12))

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

The formula assumes that 0 = no entry.

The formula will give an error if there are less than 12 entries. If that is a
problem, post back with what you want to happen if there are less than 12
entries.

=SUM(OFFSET(DataRng,MAX((DataRng<>0)*ROW(DataRng))-1,0,-MIN(12,COUNTIF(DataRng,">0"))))

array-entered would sum up to a maximum of 12 entries. In other words, if you
only had ten entries, it would sum them, but if there were twelve or more, it
would do the rolling sum.


--ron
 
Back
Top