Summing every other cell

Padintin

New Member
Joined
May 10, 2002
Messages
4
Is there a way to sum/average ever other cell or every 3rd or 4th cell and so on...?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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


ni****h desai
http://www.pexcel.com
 
Upvote 0
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))
 
Upvote 0
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.

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>Microsoft Excel - aaSum&AvgEvery.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Options(<U>O</U>) Tools(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>I12</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=2 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Start At (either 1 or D2)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>Sum Every</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range1 (in turquoise)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range1))')><FONT FACE=MS P???? COLOR=#000000>44</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range2 (in gold)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range2))')><FONT FACE=MS P???? COLOR=#000000>44</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Start At (either 1 or D2)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>14</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>Average Every</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range1 (in turquoise)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript: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))')><FONT FACE=MS P???? COLOR=#000000>11</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>18</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE= COLOR=#000000>Range2 (in gold)</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript: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))')><FONT FACE=MS P???? COLOR=#000000>11</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#00FFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>14</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>13</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>18</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>14</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Sum&AvgEvery</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically written by HtmlMakerVer1.12</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT>
</CENTER>

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top