Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Summing every other cell

  1. #1
    New Member
    Join Date
    May 2002
    Location
    California
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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))
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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=
    ABCD
    12 Start At (either 1 or D2)1
    24 Sum Every3
    36 Range1 (in turquoise):alert('=SUMPRODUCT((MOD(ROW(Range1)-ROW(OFFSET(Range1,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range1))')>44
    48 Range2 (in gold):alert('=SUMPRODUCT((MOD(ROW(Range2)-ROW(OFFSET(Range2,0,0,1,1))+($D$1=$D$2),$D$2)=0)*(Range2))')>44
    5102
    6124Start At (either 1 or D2)1
    7146Average Every3
    8168Range1 (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
    91810Range2 (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
    102012
    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •