View Full Version : Yet another Excel question

Sdallnct

02-04-03, 03:14 PM

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"! :hscratch:

MrPeanut

02-04-03, 04:08 PM

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

02-04-03, 05:16 PM

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

02-04-03, 06:02 PM

Thanks, will give it a try...

TiBoss2

02-09-03, 01:23 AM

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

02-10-03, 02:21 AM

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

02-10-03, 08:52 PM

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

02-10-03, 09:37 PM

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

Sdallnct

02-15-03, 11:20 PM

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.