Summing every other cell

[Padintin]

Is there a way to sum/average ever other cell or every 3rd or 4th cell and so on...?

[nisht]

you need the udf.........

Function sumodd(look As Range) As Double
Dim c As Range
Dim dbltotal As Double
Dim intcount As Integer

For Each c In look

If c.Row Mod 2 Then
' for even rows i.e 2nd row, 4th row
' if c.row mod 2 = 0 then
dbltotal = c + dbltotal
intcount = 1 + intcount
End If
Next c
' you need the average
'sumodd = dbltotal / intcount

sumodd = dbltotal

End Function


nishith desai
http://www.pexcel.com

[Juan Pablo González]

Actually, you can do this directly in Excel... take a look at the Tip of the Day for...

January 24th, 2002.

The archive got messed up, so here it is:

1/24/2002 9:10:07 AM | Juan Pablo Gonzalez

Jenny asks "Is there a quick way of adding every other cell ?, I have a column of about 250 cells, and I have to select them manually using Ctrl. Can you help ?"

Jenny, this is a matter of using a little math to remember how to determine if a number is even or is odd, and then translate that into the formula.

The formula
=MOD(A1,2)
would return 0 if the number is even and 1 if odd. If we use instead of A1, the ROW() function, we can know if a particular row is even or odd. Now, suppose you're trying to calculate this formula

=A1+A3+A5+A7+A9 ... etc.
or this one
=SUM(A1,A3,A5,A7...)

=SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))

Remember, this is a CSE Formula, to enter it you must press at the same time Control Shift Enter, instead of just Enter as regular formulas.

You can also use this non-array formula

=SUMPRODUCT((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))

[Aladin Akyurek]

There is always some uncertainty involved in expressions like "sum/average ever other cell or every 3rd or 4th cell." The question arises as to whether to start summing/averaging from the first cell of the target range or at Nth other cell.

I believe a well-parameterized UDF is the way to go.
A flexible UDF should accept a Range that can be either vertical (a range in a column) or horizontal (a range in a row), a Start cell (either from the first cell on or from Nth cell on), a Function parameter (at least for SUM and AVERAGE), and N which indicates (every Nth) cell to be included in Function's domain.

The sheet figure shows a bit more flexible formula approach for vertical ranges.

Microsoft Excel - aaSum&AvgEvery.xls
File(F) Edit(E) View(V) Insert(I) Options(O) Tools(T) Data(D) Window(W) Help(H)
I12		=
*	A	B	C	D
1	2	*	Start At (either 1 or D2)	1
2	4	*	Sum Every	3
3	6	*	Range1 (in turquoise)	:alert('=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range1))')>44
4	8	*	Range2 (in gold)	:alert('=SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range2))')>44
5	10	2	*	*
6	12	4	Start At (either 1 or D2)	1
7	14	6	Average Every	3
8	16	8	Range1 (in turquoise)	:alert('=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)*(Range1))/MAX(1,SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)+0))')>11
9	18	10	Range2 (in gold)	:alert('=SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$6=$D$7),$D$7)=0)*(Range2))/MAX(1,SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$6=$D$7),$D$7)=0)+0))')>11
10	20	12	*	*
11	*	14	*	*
12	*	16	*	*
13	*	18	*	*
14	*	20	*	*
Sum&AvgEvery

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by HtmlMakerVer1.12
If you want this code, click here and Colo will email the file to you.


D1 must be either 1 or equal to D2.
D2 records N of the every Nth cell.
D3 has a formula for summing:

=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range1))

D4 likewise for the second vertical range.

D8 has a formula for averaging:

=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)*(Range1))/MAX(1,SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$6=$D$7),$D$7)=0)+0))

D9 likewise for the second vertical range.

Aladin

[nisht]

Function sumodd(look As Range, t As Byte) As Double

Dim c As Range
Dim dbltotal As Double
Dim intcount As Integer
Dim intcountrange As Integer
Dim blnrc As Boolean


If t > 1 Then Exit Function

If t = 1 Then blnrc = True
For Each c In look
If blnrc Then
If c.Column Mod 2 Then
dbltotal = c + dbltotal
intcount = 1 + intcount
End If
ElseIf t = 0 Then
If c.Row Mod 2 Then
' for even rows i.e 2nd row, 4th row
' if c.row mod 2 = 0 then
dbltotal = c + dbltotal
intcount = 1 + intcount
End If
End If
Next c
' you need the average
'sumodd = dbltotal / intcount

sumodd = dbltotal

End Function


i think this can work.. and can solve the problem which aladin had mention in his answere. Thanks aladin,, for making the point.

i request suggestion for above code.

nisht
http://www.pexcel.com