Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Yet another Excel question

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

Yet another Excel question

Old 02-04-03, 04:14 PM
  #1  
DVD Talk Hero
Thread Starter
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 29,243
Likes: 0
Received 2 Likes on 2 Posts
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"!
Old 02-04-03, 05:08 PM
  #2  
Senior Member
 
Join Date: Jun 2002
Posts: 668
Likes: 0
Received 0 Likes on 0 Posts
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.
Old 02-04-03, 06:16 PM
  #3  
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 8,810
Received 0 Likes on 0 Posts
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
Old 02-04-03, 07:02 PM
  #4  
DVD Talk Hero
Thread Starter
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 29,243
Likes: 0
Received 2 Likes on 2 Posts
Thanks, will give it a try...
Old 02-09-03, 02:23 AM
  #5  
New Member
 
Join Date: Aug 2002
Posts: 15
Likes: 0
Received 0 Likes on 0 Posts
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)
Old 02-10-03, 03:21 AM
  #6  
DVD Talk Hero
Thread Starter
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 29,243
Likes: 0
Received 2 Likes on 2 Posts
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...
Old 02-10-03, 09:52 PM
  #7  
New Member
 
Join Date: Aug 2002
Posts: 15
Likes: 0
Received 0 Likes on 0 Posts
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.
Old 02-10-03, 10:37 PM
  #8  
Senior Member
 
Join Date: Jun 2002
Posts: 668
Likes: 0
Received 0 Likes on 0 Posts
will you ever need to sum 12 months, that are not the current 12 though?
Old 02-16-03, 12:20 AM
  #9  
DVD Talk Hero
Thread Starter
 
Join Date: Dec 2000
Location: Home again, Big D
Posts: 29,243
Likes: 0
Received 2 Likes on 2 Posts
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.

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


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.