Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Each week, I will delete one of the columns (it's a set of forecasts held together by a week number at the head of the column; e.g., this week is week 46).

I want to add the next x number of cells together. This (x) may change; I'll use 20 as an example. This will give me the sum of the next 20 cells in that row. I then want to do it for the next row below that, etc.

share|improve this question
1  
Could you give an example of your data please? –  terdon Nov 13 '12 at 17:18
    
It's a little hard to tell exactly what you want without more explanation (I'm unsure what deleting a column weekly has to do with what you want...), but I think you may find this helpful: superuser.com/a/300388/76571 Just replace the AVERAGE function with a SUM function. –  Excellll Nov 13 '12 at 20:17
add comment

2 Answers

if you have data organized by date, try a pivot table to massage it. You can more easily filter in the pivot field and columns, and you get to keep your historical data as a bonus. As you describe multiple values, the data would have to be transposed into one observation per line (as in forecast-monthly-v2.xlsx).

quick example: http://www.well.com/~fl/forecast/

share|improve this answer
add comment

If you want cell A1 to evaluate the sum of B1 through U1 (the next 20 cells in row 1), you could put

=SUM($B1:$U1)

into cell A1.  But this probably doesn’t do what you want when you delete column(s).

=SUM(OFFSET(A1,0,1,1,20))

might be closer to what you want.  If you want the 20 to be a parameter, enter 20 into cell Z1 (for example) and put

=SUM(OFFSET(A1,0,1,1,$Z$1))

into A1.

Of course any of the above can be replicated into subsequent rows.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.