Hi i working a fairly large spreadsheet which is broken down into months. I want to put the yearly subtotals at the begining of the spreadsheet. So in cell A1 i want to add AA2-AL2, B1 i want to add AM2-AX2 and so on so forth. How can i do this whithout manually going to each set of values.
Is there anyway i can put a formula in A1 which i can copy to B1,C1,D1,E1, which will pick up a set of 12 cells each (with no overlap)
Excel and OOCalc support defining a formula and then 'painting' it into a range of cells, where it will be adapted to each location it has wound up in. If I'm recalling correctly, both do it by clicking and dragging on a little black square to the lower right of the source cell.
You can also copy the desired formula/cell and use the Paste Special command: right click on desired cell(s) -> Paste Special.
Then choose the "Formulas" option as well as any of the other appropriate choices.
To confirm you have
2.3 | 1.2 |3.4 | 5.2 | 6.4 | 8.6
(imagine you want to get totals of 2 cells rather than 12)
a1 = 2.3 + 1.2 a2 = 3.4 + 5.2 a3 = 6.4 + 8.6
If this is what you want what you need to do is dynamically work out the range to add up using the row of the cell.
therefore a1 should be SUM( ((2 * (row of current cell-1)) + 1)|rowtosum:((2 * (row of current cell) +1)|column)
In my notation sum(a1:a3) is written as sum (column|row:column|row)
Hope this makes sense.
In Excel you would acheive this using the ROW function or possible even better with the offset function in conjuction with the indirect function.
This would be a brittle solution though that would break with row/column insertions and cut and pastes