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 ?
Another option to sum even/odd cells:
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
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.
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.
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.
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.