How can I apply an excel formula to every seventh line?
I have a spreadsheet that I use to keep tracking of my running progress. I’d like to start looking at some of the data by week rather than by day. The data are organized by columns, so I’d like to be able to, for instance, sum every seven rows (for seven days) and look at total miles or total running time per week.
I’ve tried to work out a formula using the mod function (=if(mod(row(), 7)=0, sum(C1:C7)), but I can’t quite get it to work.
What am I doing wrong, and is there an easier way to do this?
Observing members:
0
Composing members:
0
7 Answers
I just did this:
=IF(MOD(ROW(),7)=0,SUM(A1:A7),0)
and it works. your formula doesn’t have anything to do if the mod result comes up false.
I’d add a new column, say, C, so C1 is 1, but after that, =if(previous c=7,1,previous c+1). Then a new column, D, equal to =if(c1=7,sum(previous 7),0).
Hope my notation is understandable enough.
Actually, if you want weekly summaries, I’d suggest something else. For each row, have a column that gives you the end of week date. Then, on another sheet, have one row for each week and use the sumif function to aggregate the data.
@roundsquare: I could do that for the future. Right now I’m trying to figure out how to look at over a year’s worth of data, so something like 430 data points… lots of weeks to enter by hand.
The mod formula you’re using is still not working the way I want it to… but it might just be that I’m using google docs instead of real excel. Nothing works right.
@bob_, I’m giving yours a shot next if I can’t get the mod function to work soon.
Got it. Phew. Thanks guys.
@nikipedia Your’re welcome! I can flat-out guarantee that mine works, though it’s not the most elegant of solutions.
@nikipedia I wasn’t suggesting entering all the weeks by hand. Here’s one simple way to do this:
Say cell A1 is May 2. In B1, you enter May 8 and copy that from A1 to A7. For A8, put
=A1 + 7
And just copy that down. Now you’ll have the end of week for each row.
Not saying you should necessarily do this now, but just clarifying my idea.
Answer this question 
This question is in the General Section. Responses must be helpful and on-topic.
Composing members:
0
