Mark O'Brien
MrExcel MVP
- Joined
- Feb 15, 2002
- Messages
- 3,530
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
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