Release List Reviews Shop Join News DVD Giveaways Video Games Advertise
DVD Reviews | Theatrical Reviews | Adult DVD Reviews | Video Game Reviews | Price Search Buy Stuff Here
DVD Talk
DVD Reviews DVD Talk Headlines HD Reviews


Add to My Yahoo! - RSS 2.0 - RSS 2.0 - DVD Talk Podcast RSS -


Go Back   DVD Talk Forum > General Discussions > Tech Talk

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Reply
 
Thread Tools
Old 02-04-03, 03:14 PM   #1
Sdallnct
DVD Talk Legend
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 19,696
Yet another Excel question

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"!
__________________
Live Free. Dine Well. Drink good beer.

Home Theater Build
  Reply With Quote
Old 02-04-03, 04:08 PM   #2
MrPeanut
Senior Member
 
Join Date: Jun 2002
Posts: 668
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.
  Reply With Quote
Old 02-04-03, 05:16 PM   #3
dtcarson
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 8,229
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
__________________
I have nothing to say.
My dvds
360 Gamertag: dtcarson
  Reply With Quote
Old 02-04-03, 06:02 PM   #4
Sdallnct
DVD Talk Legend
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 19,696
Thanks, will give it a try...
__________________
Live Free. Dine Well. Drink good beer.

Home Theater Build
  Reply With Quote
Old 02-09-03, 01:23 AM   #5
TiBoss2
New Member
 
Join Date: Aug 2002
Posts: 15
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)
  Reply With Quote
Old 02-10-03, 02:21 AM   #6
Sdallnct
DVD Talk Legend
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 19,696
Quote:
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...
__________________
Live Free. Dine Well. Drink good beer.

Home Theater Build
  Reply With Quote
Old 02-10-03, 08:52 PM   #7
TiBoss2
New Member
 
Join Date: Aug 2002
Posts: 15
Quote:
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.
  Reply With Quote
Old 02-10-03, 09:37 PM   #8
MrPeanut
Senior Member
 
Join Date: Jun 2002
Posts: 668
will you ever need to sum 12 months, that are not the current 12 though?
  Reply With Quote
Old 02-15-03, 11:20 PM   #9
Sdallnct
DVD Talk Legend
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 19,696
Quote:
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.
__________________
Live Free. Dine Well. Drink good beer.

Home Theater Build
  Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


All times are GMT -5. The time now is 02:56 PM.

Rules - DVD Talk - Archive - Privacy Statement - Top

Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.2.0
Copyright 2011 DVDTalk.com All Rights Reserved. Privacy Policy and Terms of Use.