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 ?
Ihm replies:
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.
If you’re like me, you’re probably saying WTF. Here’s how it works:
The first argument of NPV is rate. By putting two numbers in brackets, he created an array. Since the array has two elements, NPV will calculate twice, once for each rate provided.
When the rate is zero, NPV simply returns the number. Think of it like this: If you had 10 payments of $1 at 0% interest, you’d have $10. So the first run-through of NPV is NPV(0,A1:A250) and that is equivalent to SUM(A1:A250).
The fun part comes with the second run-through of NPV. The formula for NPV, per Excel help, is
The key part here is the denominator. Setting the rate to -2 makes that denominator -1^i. As i moves from 1 to n (n being 250 in this example), the denominator equals -1 when i is odd and 1 when i is even (-1^2=1, -1^3=-1). Dividing the values(i) by -1 or 1 returns -values(i) and values(i) respectively – and alternately in this case. Let’s look at an example with less rows.
where A1:A3 is 1,2,3.
We know that when the rate is zero it’s the same as summing. That gives us 6.
| rate=0 |
|---|
| 1 |
| 2 |
| 3 |
| 6 |
When the rate is 2, our denominator will be -1 and +1 alternately. The first value (i=1,value(1)=1) will be divided by -1 (because i is odd) and we get -1. The second value (i=2, value(2)=2) will be divided by +1 and return 2. The third value (i=3, value(3)=3) will be -3.
| rate=0 | rate=-2 | Net |
|---|---|---|
| 1 | -1 | 0 |
| 2 | 2 | 4 |
| 3 | -3 | 0 |
| 6 | -2 | 4 |
Take that total and divide by 2 and you get the answer: 2. What happened is that odd numbered rows were negated so that they summed to zero and even number rows were doubled (which is why we have to divide by 2 at the end). Here’s a different example:
where A1:A4 is 12,46,27.
The arguments 2-30 of NPV are the values. By leaving the second argument blank, NPV will treat it as zero. With i=1, value(i)=0, now A1 will be i=2 (and even number) where as in the last example it was odd. Sticking a zero in i=1, shifted our values from the range to the next i, which is the opposite (from and odd/even point of view) than it would be if we did not put that zero in there.
| rate=0 | rate=-2 | Net |
|---|---|---|
| 0 | -0 | 0 |
| 12 | 12 | 24 |
| 46 | -46 | 0 |
| 27 | 27 | 54 |
| 85 | -7 | 78 |
We negated every other number and doubled every other number. Divide the result by 2 and you get 39 (12+27).
Remember, with great formula knowledge comes great formula responsibility. Please formulate responsibly.
Very clever, but I really think it would be easier to insert a column of 1s in alternating rows, then use sumproduct.
I agree with Doug. In business, it is important to make things clear and checkable, so the simpler the better.
Although I’ve been a power user for nearly 20 years, that formula would totally confuse me (NPV? WTF?), so although it is very clever, I would not recommend using it.
For summing every other row, it might be easier to enter 1s AND 0s in every other row, but this approach becomes problematic when summing every Nth row. There are times when generality beats simplicity. That’s why we usually don’t write 12 as ++++ ++++ || .
I would have a column numbered 1, 2, 3,…down the rows, and then use a mod function to pick out every Nth row, where N can be a variable.
I don’t think it’s too hard to copy down as many blocks as required of 1 followed by n-1 blanks, but if I had to do this often enough for it to be worthwhile doing something more complicated I’d write a UDF.
=SumSkip(SumRange, Numbertoskip, Optional FirstRow)
Heresy, I know :)
A more simpler solution is to use SUMPRODUCT and MOD
=SUMPRODUCT((MOD($A$1:$A$200,2)=0)*($A$1:$A$200))
=SUMPRODUCT((MOD($A$1:$A$200,2)0)*($A$1:$A$200))
this would give the same result
Oops, wrong formula, meant to include ROW()
One can always generate a series of 1 and zeros so that every {N}th element is 1 (starting with element 1) and then use sumproduct with =SUMPRODUCT(A2:A26,1*(MOD(ROW(A2:A26)-ROW(A2),{N})=0)). Tested with Excel 2007.
I hate it when people explain magic tricks.
Ignore my previous comments, it was a coincidence that the data was odd and even.
here is a solution that makes use of Array formulas
{=SUMPRODUCT((MOD(ROW($A$1:$A$15),2)=0)*($A$1:$A$15))}
{=SUMPRODUCT((MOD(ROW($A$1:$A$15),2)0)*($A$1:$A$15))}
it established whether the row has a mod or not and then multiplies it by 0 or 1 and totals them all up
I not that the NOT EQUALS is disappearing from the second formula, you have to include the greater than and less than signs between 2) and zero in the second formula
I’d create a temporary column of 1s and 0s
=mod(A2,2)
Ķand do a sumproduct of the two columns
Way too complex to actually use, nobody would understand it. Far easier to use
{=SUM(IF(MOD(ROW(A1:A8),2),A1:A8,0))}
as an array formula [or whatever range you want].
For those of you preferring the SUMPRODUCT solution, you can always do
=SUMPRODUCT(A1:A8,{1;0;1;0;1;0;1;0})
(which is hard to type, but interesting)
or
=SUMPRODUCT(A1:A8,MOD(ROW(A1:A8),2))
(which is basically the same as the array formula, but not an array formula)
This does the job,
=SUMPRODUCT((MOD(ROW(A1:A250),2)=0)*A1:A250)
Regards
I’m suddenly inpired to create an XLL with a formula called WTF and have it return the result of some randomly selected formula.
@Elias
Nicely brief yet flexible. Changing the Mod’s second argument gets every nth and changing the 0 to 1 gets the odds. Some may loose sight of the result being dependent on the row the data is in rather than its relative position to the total. If A1:A251 (with the SUMPRODUCT in A251) is copied down one cell, the sum will be different.
This stuff is too much fun!!
Brett
How about:
=SUM(rng*ISODD(ROW(rng)))
@Brett
How about this.
=SUMPRODUCT((MOD(ROW(A1:A250)-ROW(A1)+1,2)=0)*A1:A250)
Regards
Hi David,
Do you still maintain a site…. Still searching something like EEE letter.
Warm Regards
Kanwaljit
@Elias,
Very nice. I’ll thoroughly research an Excel formula before writing a UDF. Thanks.
@David,
Also fun! That’s an array formula, but the isodd (or iseven) is good self-documentation.
@Joe,
I have only gone in depth with formula explanations with two work associates. Each time the Sumproduct was easier to understand than the array. I’m not sure why.
Thoughts?
Brett
After being “retired” from high-level Excel stuff for a while (never had a job connected with it, though), I am now working on starting a consulting/training business. I have been doing an exhaustive search for code and new ideas, but there are not as many as you might expect. That makes the reappearance of EEE unlikely. I am also not yet into the new learning curve with VSTO, which is the only new thing out there. I do have to give Dick congrats on creating and running a web site that generates new ideas. This site is the closest thing to the EEE concept. That said, I will be contributing more to this site in the future.
Dick, thanks for picking this up. It’s based on a simple math identity: (-1)^n is 1 for even n and -1 for odd n, whereas 1^n is always 1, so adding a series to its alternating series counterpart gives twice the sum of the even terms.
For some there may be tradeoffs with readability but for the average user many formulas are black boxes anyway and this is far quicker to recalculate and also shorter so easier to maintain than many other alternatives. Why not take advantage of such shortcuts where possible?
@David,
I loved the Excel work I do as an actuary so much I left the corporation and want to help people reap the savior from mind-numbing manual processes.
That corp is moving the tracking of maintenance testing in their heath product rating system (there was something enhanced every quarter) from a Lotus email database to a Sharepoint server and informal conversations indicate they will move to Office 2007 eventually. Do you think there will be a burgeoning of training for even everyday office use of Excel 2007? I dove into it at home and was pleasantly surprised that all of the previous keyboard hotkeys were still supported. There is a happy dialog that displays the office 2003 access sequence I’m following. I’m getting used to it, but I can’t yet programmatically change the ribbon. Lucky people are mostly on 2003 or prior. Best of fortunes on the business.
@Brett
I’d guess it’s because people generally don’t understand array formulas in concept. The way the SUM array formula works, as compared to how the SUMPRODUCT formula works, are basically the same; the difference is that SUMPRODUCT takes an array as an argument, while SUM does not, so you need to use the ctrl+enter combination to execute it. Other than that they basically do the same thing… SUMPRODUCT just takes the work of doing the array formula away from you.
If they can actually understand what the SUMPRODUCT version does, I’d suggest that it’s a good way to explain array formulas, really – since they really do both do basically the same exact thing. [Unless the nested IF is causing the trouble, in which case that’s a different issue entirely.]
“Ķ SUMPRODUCT just takes the work of doing the array formula away from you.”
More accurately, SP removes the need to array-enter the formula, that is about it, but it is enough to make explaining a solution a lot easier with SP. And it was with SP that most of these innovations have happened.
There are some things that you cannot do with SP because an array resolves to an error, then you have to use an arfray SUM(IF to outsort the offenders.
Hi David,
See you more here in the future…… May God bless you with 1000 healthy years !
and here is Bob……..
Father of “SUMPRODUCT”
Thanks a Ton Bob. Everything I knew about SUMPRODUCT is just because of yours contribution. Thanks..
Kanwaljit
[…] Adding Every Other Cell¬ (also at the Microsoft Office Blog: Adding Every Other Cell in a Column ) and Summing the Digits of a Number […]
here’s some code I wrote for adding like values together:
100111 would be 11 20 31
111111 would be 61
000101 would be 30 11 10 11
Hi to all,
One more {array formula} :
=SUM(IF(ISEVEN(ROW(A1:A250)),A1:A250))
Enter with CSE
and same with No-CSE:
=SUMPRODUCT((ISEVEN(ROW(A1:A250)))*(A1:A250))
Regards,
Khalid