You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s author is Bill Jelen, author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.
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))
MrExcel provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Comments: (9) Collapse
Perhaps somewhat easier to debug...
=SUMPRODUCT(1*ISEVEN(ROW(A1:A250)),A1:A250)
=SUMPRODUCT(1*ISODD(ROW(A1:A250)),A1:A250)
@Ed - except that you have to know that Excel's SUMPRODUCT doesn't automatically convert boolean TRUE/FALSE into numeric 1/0, respectively. FWIW, OpenOffice/StarOffice Calc does convert boolean values to numbers, so it can return numeric results other than 0 for
=SUMPRODUCT(ISEVEN(ROW(A1:A90));A1:A90)
and
=SUMPRODUCT(ISODD(ROW(A1:A90));A1:A90)
Another option to sum even/odd cells:
=SUM(NPV({0,-2},A1:A250))/2
=SUM(NPV({0,-2},,A1:A250))/2
Use --A1:A250 and array-enter in case of blanks.
Given that Jenny has manually selected the cells she wants to add up with CTRL, she could have simply defined a range name (Formulas-Name Manager), as these can include non contiguous cells. (the cells for the named range could also be manually entered in the name manager)
If she just needs to see the sum, selecting the range will immediately show total (and average) at bottom of sheet. Or a simple =SUM(Range_name_) formula can easily sum a range without the rather complex abstractions given above.
The advantage of this approach is that it can be used to sum any non-contiguous set of cells, not just 'every other row'. The disadvantage is that the cells do need to be manually selected the first time in most situations.
The iseven/isodd functions are easier to grasp for most people than 'MOD', as already indicated by Ed.
Why make things complicated?
Guys, what version of Excel are you using that ISEVEN/ISODD works on arrays?
Excel 2002:
=SUMPRODUCT(1*ISEVEN(ROW(A1:A250)),A1:A250) = #VALUE!
=SUMPRODUCT(--(ISEVEN(ROW(A1:A250))),A1:A250) = #VALUE!
=SUMPRODUCT(--(ISEVEN(A1:A10))) = #VALUE!
CSE =SUM(IF(ISEVEN(A1:A10),A1:A10)) = #VALUE!
It works on 2007, but of course Ed's formula needs something to coerce the booelans to 1/0.
BTW, it works on 2007 because these are ATP functions and they have been integrated into Excel, in 2003 and earlier they are part of the addin.
>It works on 2007
Ok, but why does this work:
=SUMPRODUCT(--(ISEVEN(ROW(A1:A5))),A1:A5)
But these don't work:
=SUMPRODUCT(--(ISEVEN(A1:A5)),A1:A5) = #VALUE!
=SUMPRODUCT((ISEVEN(A1:A5))*A1:A5) = #VALUE!
CSE: =SUM(IF(ISEVEN(A1:A5),A1:A5)) = #VALUE!
CSE: =SUM(--(ISEVEN(A1:A5))*A1:A5) = #VALUE!
It seems that if you use a range array as the argument it doesn't work.
This also works:
CSE: =SUM(IF(ISEVEN({1;2;3;4;5}),1))
Note that the formulas I gave in my previous response were from OpenOffice Calc rather than Excel.
Interesting. What do
=SUMPRODUCT(--ISEVEN(ROW(A1:A5)))
=SUMPRODUCT(--ISEVEN(A1:A5))
return respectively? If the first returns a number (assuming A1:A5 contains at least one number) but the second returns #VALUE!, then it'd appear ISEVEN and presumably also ISODD distinguish (wrongly! inexplicably!) between arrays and ranges. That'd imply that the bright bulbs who rewrote ISEVEN and ISODD as built-in functions rolled their own iterator rather than using the iterator used by the functions that were built into previous Excel versions.
Comments: (loading) Collapse