Adding every even row/odd row

[Mark O'Brien]

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/viewtopic.php?t=9380&highlight=simpsons+rule

 

[rrdonutz]

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

 

[Brian from Maui]

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

 

[Mark O'Brien]

...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.

 

[Aladin Akyurek]

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

 

[Mark W.]

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.

 

[Mark O'Brien]

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)

 

[Aladin Akyurek]

....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.

 

[tusharm]

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!

 

[Mark O'Brien]

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 damned ideas down.