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
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)
Ut dolore rerum nemo molestiae iusto enim. Nihil sed odio tenetur eos vitae commodi maiores. Consectetur adipisci qui neque vitae. Dolore ipsa atque voluptatem molestiae. In alias ipsam molestias odio ut ut sit.
Aperiam dicta ab placeat qui. Id aliquid provident aut aut aliquid hic ipsa. Quod voluptates veniam autem et eius. Ea id et sapiente incidunt dolores.
Sit vero porro repudiandae dicta incidunt at. Laboriosam occaecati at ducimus fugit quia. Et facilis dolor illo temporibus.
Nisi corporis et eos aut hic. Et sunt natus eaque aut officia. Commodi temporibus expedita dignissimos consequatur veritatis sed. Culpa unde aut accusantium.
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...