Adding the next x number of cells in a row together

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.

-
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

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/

-

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.

-