Excel Question - "upkeep" column?
I keep a bunch of data on every single time I go running, including date, pace, temperature, distance, etc. One of the pieces of data I keep track of is called "upkeep" and it is the sum of my distances ran over the past 30 days. Since I do not go running every single day I can't just use a simple formula to update the "upkeep" column [=sum(a3:a33)]. Also, since months consist of 27, 30, or 31 days, I can't just mentally sum the numbers from 1/20/09 to 2/20/09. So what I have been doing up to this point is the best solution I have thought of: I have conditional formatting on the dates that highlights them if they satisfy [ =A6>TODAY()-30 ]. Then, when I add a new entry I have to go through and manually sum up the distances of the highlighted cells (past 30 days).
I will comment on one thing, and then ask a question.
Comment: I know, I'm a loser and should be spending my time studying or doing something more productive. Oh well.
Question: Is there any way to remove the manual labor from updating the "upkeep" column? If it is possible, I would prefer a solution that doesn't involve macros since I mainly update this spreadsheet from my mac with office 2008 -- However, if it requires macros, that is fine.
=SUMIF(A1:A7,">"&(OFFSET(B7,0,COLUMN(A1)-COLUMN(B1))-30),C1)
Column A - dates Column B - upkeep Column C - the stuff being summed
a1:a7 -> all dates, resize as necessary b7 -> current item
replace columns as needed, resize references too.
1) there are automated programs to do this, you know 2) here is what I just wrote up, probably more convoluted than it has to be but you can play with it
Assume x refers to the line value of that cell Enter in F1 =TODAY() Enter in F2 =EDATE(F1,-1) Enter in Column A =DATE(YYYY,MM,DD) (fill in appropriate value for that day) Enter corresponding mileage in Column B Program Column C as =EDATE(Ax,0) where x is the column value, ie =EDATE(A1,0), etc. Hide this column Program Column D as =IF(CX>F2,"Recent","Old"). Hide this column. In the spot where you want your upkeep column, enter =SUMIF(D1:D99999,"Recent",B1:B99999)
There are easier ways, you can streamline it once you do that. I'm pretty sure you can enter a straight sumif condition without the column D, but my excel was being pissy about that
Column A = Date Column B = Upkeep Column C = Summation
=if(today()-30=A2,B2,0) =if(today()-30=A3,B3,0) =if(today()-30=A4,B4,0) etc....
At the bottom of column C, sum the entire column.
Dacarez and Drex - I think your ways work, but I feel like mine is more simple. I admittedly need to learn to use the offset function, though.
Not sure if I'm understanding this correctly but:
add a column (you can hide it afterwards) so that upkeep is in column b. A3 will be =if(b3>today()-30,1,0). Then you can do the sum in column B i.e. =sumif(A3:A33,"=1",B3:B33)
Sed praesentium magni quam eos atque. Ut numquam culpa nam ea at. Exercitationem illum inventore et assumenda minima.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...