Yet another Excel question

[Sdallnct]

I thought I knew how to do this, as it seems easy enough, but I give up....

I need to keep track of the days I work (no I don't work every day), but I need to do so on a ROLLING 12 months basis. It is ok to do it by MONTH, Example:

2002 2003

Jan 20 0
Feb 10 2
March 30
April 20
May 10
June 20
July 20
Aug 20
Sep 20
Oct 20
Nov 5
Dec 0

Total 195 Total 0 Rolling Total (previous 12 months) 177

I know this is basic and I should know how but I "can't make it go"!

[MrPeanut]

do you need to do it in collumns like that? what if it was just one list
Jan 02
Feb 02
...
Dec 02
Jan 03
Feb 03
...

then it would be easy to take the sum of the last 12. not the best solution, i know. although, with some links you could format it your way, just enter the data my way.

[dtcarson]

There may be a different way to do it, but I do it with dynamic named ranges [for graphing purposes.]
Do a search for "dynamic named range" or "name range", or check out http://www.beyondtechnology.com/geeks007.shtml

[Sdallnct]

Thanks, will give it a try...

[TiBoss2]

lets say

column A is your month
column B is your 2002
column C is your 2003

and your data starts in row 1 and ends in row 12

Try this formula

=SUMIF(C1:C12,">=0",C1:C12) + SUMIF(C1:C12,"",B1:B12)

explanation:

sum the values of column C or 2003 if the value in your 2003 column is >= 0, that means if you worked through mar 2003, then you have values >=0 for those 3 months so sum the values from Jan to Mar 2003

plus the sum of the values of column B when your column c is blank, meaning from Apr to Dec 2003, you have not worked yet so the cell value is blank then you add column B

in other words,
if you have a value (>=0) in column 2003, use those values otherwise use the 2002 values when you don't have values yet for the 2003 (blank)

[Sdallnct]

Originally posted by TiBoss2
lets say

column A is your month
column B is your 2002
column C is your 2003

and your data starts in row 1 and ends in row 12

Try this formula

=SUMIF(C1:C12,">=0",C1:C12) + SUMIF(C1:C12,"",B1:B12)

explanation:

sum the values of column C or 2003 if the value in your 2003 column is >= 0, that means if you worked through mar 2003, then you have values >=0 for those 3 months so sum the values from Jan to Mar 2003

plus the sum of the values of column B when your column c is blank, meaning from Apr to Dec 2003, you have not worked yet so the cell value is blank then you add column B

in other words,
if you have a value (>=0) in column 2003, use those values otherwise use the 2002 values when you don't have values yet for the 2003 (blank)

Dude...can I buy you a beer??? I just did it real quick and havn't played with every variable, but it looks like it works perfect.

I love to expain why I have to keep track like this. But is really long and boring even for me. And while my tracking is not the "official" one, it helps me in my discussions with others, and keeps me prepared for work.

Thanks again...

[TiBoss2]

Originally posted by Sdallnct
Dude...can I buy you a beer??? I just did it real quick and havn't played with every variable, but it looks like it works perfect.

I love to expain why I have to keep track like this. But is really long and boring even for me. And while my tracking is not the "official" one, it helps me in my discussions with others, and keeps me prepared for work.

I know this is basic and I should know how but I "can't make it go"!

Thanks again...

Good to know it works. I've been programming Excel for 3 years now, and this one is not close to being a basic problem.

[MrPeanut]

will you ever need to sum 12 months, that are not the current 12 though?

[Sdallnct]

Originally posted by MrPeanut
will you ever need to sum 12 months, that are not the current 12 though?

Not sure what you are asking. Basically, once we got to Febuary of '03, I'm no longer in need of January of '02. However, I did create a chart that kept totals for '02 and and is keeping total for '03 in a calander year PLUS used the above formula to figure the rolling 12 month period.