Results 1 to 10 of 10

Adding every even row/odd row

This is a discussion on Adding every even row/odd row within the Excel Questions forums, part of the Question Forums category; First of all, a tip of the hat to Aladin's HOF entry. That made me think about SumProduct for this. ...

  1. #1
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Adding every even row/odd row

    First of all, a tip of the hat to Aladin's HOF entry. That made me think about SumProduct for this. I have two questions, one related to the problem and one related to my solution.

    http://www.mrexcel.com/wwwboard/messages/8961.html

    What I would like to achieve is to add every other row. i.e. I want to add all of the odd numbered rows and I want to add all of the even numbered rows. (The larger problem is that I am going to be using Simpson's Rule)

    My simple data example is contained in A1:A10, {1, 2, 3, 4, 5, 6}

    My first attempt at a formula resulted in the Array formulae:

    Odd rows
    =SUMPRODUCT(A5:A10,MOD(ROW(A5:A10),2))

    Even rows:
    =SUMPRODUCT(G5:G10,N(NOT(MOD(ROW(G5:G10),2))))

    I thought about using a version incorporating the ISODD or ISEVEN functions in Anaysis Toolpak but this spreadsheet isn't just for me.

    My first question is thus:

    I found that I had to incoporate "N" to force the "NOT" to evaluate as numbers. Why? I thought TRUE and FALSE could be used in this manner, especially as my "Odd" formula evaluates the MOD(ROW(A5:A10)) as {1,0,1,0,1,0}


    My second question is:

    Is there a better formula to achieve this?

    I may end up reverting to VBA for the actual Simpson's Rule function, but this is just another exercise for me to broaden my abilities.


    BTW, for those with a long memory, I was working on something similar over a year ago.

    http://www.mrexcel.com/board2/viewto...=simpsons+rule

  2. #2
    Board Regular
    Join Date
    Jan 2003
    Location
    Round Rock, Texas
    Posts
    564

    Default Re: Adding every even row/odd row

    Mark, concerning--
    Even rows:
    =SUMPRODUCT(G5:G10,N(NOT(MOD(ROW(G5:G10),2))))
    Why not:

    =SUMPRODUCT(G5:G10,MOD(ROW(G5:G10)-1,2))

    (Don't know how or if this will help you with a Simpson's Rule function.)

    BOL,

    --Tom

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,151

    Default Re: Adding every even row/odd row

    Array entered for even rows

    =SUM(IF(MOD(ROW(A1:A8),2)=0,A1:A8))

    for odd rows

    =SUM(IF(MOD(ROW(A1:A8),2)=1,A1:A8))

    or are you wanting a sumproduct formula?

    I probably got this from Aladin also

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: Adding every even row/odd row

    ...because I hadn't thought of that. Thank you.

    You've also highlighted that I have two different ranges in my original post. Oops. Assume A5:A10 from now on.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,520

    Default Re: Adding every even row/odd row

    Quote Originally Posted by Mark O'Brien
    First of all, a tip of the hat to Aladin's HOF entry. That made me think about SumProduct for this...
    Thanks Mark.

    Some time I have to add the following formula to the list of the formulas that link discusses, which it post-dates:

    =SUMPRODUCT((MOD(ROW($A$1:$A$10)-CELL("Row",$A$1:$A$10)+H1,H2)=0)*($A$1:$A$10))

    H1 set to 0 and H2 to 2, this formula will some every 2nd entry in A1:A10, starting with the value in A1.

    H1 set to 1 and H2 set to 2, it will sum every 2nd entry starting with 2nd value, that is, with the value in A2.

    Note this formula is robust against any row insertion before the current data.

    Aladin

    PS. Try with your data...

    =SUMPRODUCT((MOD(ROW($A$5:$A$10)-CELL("Row",$A$5:$A$10)+0,2)=0)*($A$5:$A$10))

    and

    =SUMPRODUCT((MOD(ROW($A$5:$A$10)-CELL("Row",$A$5:$A$10)+1,2)=0)*($A$5:$A$10))

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Adding every even row/odd row

    Instead of using NOT as an inverter for the evens, consider using...

    =SUMPRODUCT(A5:A10,MOD(ROW(A5:A10),-2)+1)

    EDIT: I noticed after the fact that this is just a variation of Tom's posting.

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: Adding every even row/odd row

    Thank you all for the responses. I've currently adopted Tom's suggestion for the second formula (oh OK, Mark W. it's your solution too)

    I'll try Aladin's suggestions as well when I get back from vacation. The row insertion problem was my main concern, but I forgot to explicitly state that because my OP became rather long.

    Brian, I don't know how I missed you response, I must have been typing at the same time as you. I'm normally good at checking that sort of stuff though. I'll have a look at that as well, because I'm crap with array formulas. (...even though I think SUMPRODUCT is a built-in array formula function, as is SUMIF)

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,520

    Default Re: Adding every even row/odd row

    Quote Originally Posted by Mark O'Brien
    ....I'll try Aladin's suggestions as well when I get back from vacation. The row insertion problem was my main concern, but I forgot to explicitly state that because my OP became rather long...
    Your post has sounded a bit as if you were wedded to native row numbers. It seems not and neither do the formulas I posted.


    I'll have a look at that as well, because I'm crap with array formulas. (...even though I think SUMPRODUCT is a built-in array formula function, as is SUMIF)
    The formulas confirmed with control+shift+enter and SumProduct formulas operate on (computed) arrays, which makes them alike. Both accept/process array as well as range objects. SumIf does not accept array objects as reference.

  9. #9
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,883

    Default Re: Adding every even row/odd row

    Hi Mark,

    A way to implement Simpson's Rule with named formulas.

    I've broken down everything into incremental components, largely so that I'd understand what I was doing. An additional benefit is that this note should be shorter.

    Suppose the formula is in B1 (as a literal, not a formula). Suppose the limits of integration are in D1 and D2. Suppose the number of intervals (n, which should be even) is in F1. Then with the foll. named formulas, =SimpsonRslt will give you the desired result.

    myFX =Sheet1!$B$1
    XMax =Sheet1!$D$2
    XMin =Sheet1!$D$1
    nbrVals =Sheet1!$F$1
    incrSteps =(ROW(OFFSET(Sheet1!$A$1,0,0,nbrVals+1,1))-1)
    X =(XMin+incrSteps*(XMax-XMin)/nbrVals)
    YVals =EVALUATE(myFX&"+X*0")
    FourTwos =IF(MOD(incrSteps,nbrVals)=0,1,IF(MOD(incrSteps,2)=1,4,2))
    SimpsonVals =(FourTwos)*(YVals)
    SimpsonRslt =(XMax-XMin)/(3*nbrVals)*SUM(SimpsonVals)

    This has been tested for x^2, x^3, x^2+x+10, and sin(x).

    Finally, my original formulation used the name XVals instead of X and YVals was =EVALUATE(SUBSTITUTE("X",XVals)). However, for some bizarre reason SUM(SimpsonVals) gave the wrong result. The SimpsonVals when shown in a worksheet range was correct but, but SUM(...) yielded the wrong result. Don't ask why. So, after an appropriate amount of struggling I visited Stephen Bullen's site and copied his implementation!

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: Adding every even row/odd row

    Thanks for taking the time to concoct that post Tushar. It looks much better than what I was going to do.

    I've decided to take up my own challenge of doing this spreadsheet without VBA, except for automating the data retrieval from a *.csv and enforcing my own file naming convention. I've already been looking at your website to refresh my memory on dynamic named ranges and to look at my chart options.

    I've got a couple of other graphing projects from which I believe I can eliminate my macros. The most annoying one was an existing project, that I modified in my head whilst driving to a power plant. Then I got too busy to write the ****ed ideas down.

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
  •  


DMCA.com