Excel question

[Nottom]

OK I have a long list of data points and would like to find the sum of each group of 100.

i.e. I want the sum of the 1st 100, 2nd 100, 3rd 100, etc

Obviously for a long list 20K+, I don't want to have to type "=SUM(A1:A100)", "=SUM(A101:A200), etc"

How can I do this easily?

[TheIrishThug]

Open excel
Tools>Marco>Visual Basic Editor
On the left there should be a menu that is titled VBAProject. Right click in there and select Insert>Module
In the window that appears paste the following. You can adjust the variables as necesary. Then Run>Run Sub/UserForm

<font class="small">Code:</font><hr /><pre> Sub WriteFormulas()
Dim RowStart As Integer
Dim ColumnForSum As String
Dim Grouping As Integer
Dim RowsToSum As Integer
Dim FormulaColumn As String
Dim FormulaFirstRow As Integer
Dim SumRowNumber As Integer
Dim FormulaRowNumber As Integer
'---------------------------------
' Set Variables
'---------------------------------
RowStart = 1
ColumnForSum = "A"
Grouping = 100
RowsToSum = 500
FormulaColumn = "B"
FormulaFirstRow = 1
'---------------------------------
SumRowNumber = RowStart
FormulaRowNumber = FormulaFirstRow
While (SumRowNumber &lt;= RowStart + RowsToSum)
Range(FormulaColumn &amp; FormulaRowNumber).Formula = "=sum(" &amp; ColumnForSum &amp; SumRowNumber &amp; ":" &amp; ColumnForSum &amp; (SumRowNumber + Grouping - 1) &amp; ")"
SumRowNumber = SumRowNumber + Grouping
FormulaRowNumber = FormulaRowNumber + 1
Wend
End Sub
</pre><hr />

[Nottom]

Thanks.

I get an overflow error if I try to do it with more than 30K rows or so however, guess I'm just trying to push poor excel too hard [img]/images/graemlins/frown.gif[/img]

[TheIrishThug]

Change the "As Integer" to "As Long"
that'll let u do 2,147,486,647 rows