Formula Forensics 022. Sum the Odd Numbers between 1 and 100

Formula Forensics 022. Sum the Odd Numbers between 1 and 100:
Last week at the Chandoo.org Forums, Sunita, posed the question:
“Please help me to find out the sum of odd numbers in a range of 1-100 numbers
Like 1+3+5+7+ … 97+ 99
How it will find through an excel formula?”
I chipped in with two array formulas:
=SUM(2*ROW(OFFSET($A$1,,,100/2))-1) Ctrl Shift Enter
and

=SUM(ROW(1:100)*MOD(ROW(1:100),2)) Ctrl Shift Enter
Lets look at each of these in turn.
As usual at Formula Forensics you can download a Sample File here and follow along Download Sample File.

Formula 1: =SUM(2*ROW(OFFSET($A$1,,,100/2))-1)

The first formula we will examine is:
=SUM(2*ROW(OFFSET($A$1,,,100/2))-1) Ctrl Shift Enter
This formula works on the principle of making an array of the odd numbers between 1 and 100 and the adding them up.
We can make an array of the odd numbers from 1 to 100 by:

1. First make an array of all numbers from 1 to 50
2. Second double the array values
3. Subtract 1.
4. Add up the values

1. Make an Array from 1 to 50

The formula ROW(OFFSET($A$1,,,100/2)) can be used to make an array of the numbers from 1 to 50
In a spare cell, D4, type =ROW(OFFSET($A$1,,,100/2)) then press F9 not enter
Excel will respond with an array: ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50}

How does this work?

Offset($A$1,,,100/2) sets up a Range from A1 with no Row or Column offset, but with a height of 100/2 = 50.
In a spare cell, D6, type =OFFSET($A$1,,,100/2) then press F9 not enter
Excel will respond with an array: ={0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
We can see that the array contains 50 Zero’s (You can count them to check).
True.
But it is 50 Rows of Zero’s. The ;’s in the array separate Rows.

So the expanded formula: =ROW(OFFSET($A$1,,,100/2))
Returns the Rows of the Array Elements, not the Array Values.

2. Double the Values

The formula 2*ROW(OFFSET($A$1,,,100/2)) is used to double the array values
In a spare cell, D8, type =2*ROW(OFFSET($A$1,,,100/2)) then press F9 not enter
Excel will respond with an array:  ={2;4;6;8;10;12;14;16;18;20;22;24;26;28;30;32;34;36;38;40;42;44;46;48;50;52;54;56;58;60;62;64;66;68;70;72;74;76;78;80;82;84;86;88;90;92;94;96;98;100}

3. Subtract 1

The formula 2*ROW(OFFSET($A$1,,,100/2)) -1 is used to subtract a value of a from the array values
In a spare cell, D10, type =2*ROW(OFFSET($A$1,,,100/2)) -1 then press F9 not enter
Excel will respond with an array:  ={1;3;5;7;9;11;13;15;17;19;21;23;25;27;29;31;33;35;37;39;41;43;45;47;49;51;53;55;57;59;61;63;65;67;69;71;73;75;77;79;81;83;85;87;89;91;93;95;97;99}

4. Add up the Values

The formula =Sum(2*ROW(OFFSET($A$1,,,100/2)) -1) is used to add up the array values
In a spare cell, D12, type =Sum(2*ROW(OFFSET($A$1,,,100/2)) -1) then press F9 not enter
Excel will respond with a value of = 2500, The Answer.

 

Formula 2: =SUM(ROW(1:100)*MOD(ROW(1:100),2))

The second formula we will examine is:
=SUM(ROW(1:100)*MOD(ROW(1:100),2)) Ctrl Shift Enter
This formula works by constructing an array of values between 1 and 100 and then multiplying that Array by an Array of the Odd values between 1 and 100 and then adding up the resultant Array.
Lets start with: =SUM(ROW(1:100)*MOD(ROW(1:100),2))
Note that the Row(1:100) is used twice in the formula.
In a spare cell: D17 type: =Row(1:100) then press F9 not enter
Excel will respond with an array:  ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100}
An array of the values from 1 to 100.

Next we will look at the Mod section =SUM(ROW(1:100)*MOD(ROW(1:100),2))
In a spare cell: D19 type: =Mod(Row(1:100),2) then press F9 not enter
Excel will respond with an array:  ={1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0}

Mod returns the remainder after dividing the first parameter by the second
Eg: Mod(5,2)=1 5 divided by 2 = 2 Remainder 1.
So in our example Mod( Array, 2 ) returns the value 1 for the Odd Values and 0 for the Even values.

Next we multiply the 2 arrays together: =SUM(ROW(1:100)*MOD(ROW(1:100),2))
This is the same as:
={1;2;3;4;5; … ;97;98;99;100} * {1;0;1;0;1; … ;1;0;1;0}

In a spare cell: D21 type: =ROW(1:100)*MOD(ROW(1:100),2) then press F9 not enter
Excel will respond with an array:  ={1;0;3;0;5;0;7;0;9;0;11;0;13;0;15;0;17;0;19;0;21;0;23;0;25;0;27;0;29;0;31;0;33;0;35;0;37;0;39;0;41;0;43;0;45;0;47;0;49;0;51;0;53;0;55;0;57;0;59;0;61;0;63;0;65;0;67;0;69;0;71;0;73;0;75;0;77;0;79;0;81;0;83;0;85;0;87;0;89;0;91;0;93;0;95;0;97;0;99;0}

Finally we can add up the array values: =SUM(ROW(1:100)*MOD(ROW(1:100),2))
In a spare cell: D23 type: =SUM(ROW(1:100)*MOD(ROW(1:100),2)) then press F9 not enter
Excel will respond with a value of = 2500, The Answer.

Variation 1:

In the above formula =SUM(ROW(1:100)*MOD(ROW(1:100),2)) we described a method of evaluating Array values as either Odd or Even using the Mod function.
Excel has a built in function for determining if a Value is Odd and that is Isodd()
We can modify the above equation to use Isodd() as follows
=SUM(ROW(1:100)*ISODD(ROW(1:100)))
You can check it in cell D28.

What if I want to Sum the Even numbers?

We can use the variation described above to quickly add up the even numbers between 1 and 100
=SUM(ROW(1:100)*ISEVEN(ROW(1:100)))
In a spare cell: D21 type: =SUM(ROW(1:100)*ISEVEN(ROW(1:100)))  then press F9 not enter
Excel will respond with a value of = 2550, The Answer.

How Else Can You Solve Sunita’s Problem?

Can you solve Sunita’s problem another way?
Let us know in the comments below:

Download

You can download a copy of the above file and follow along, Download Here.

Formula Forensics “The Series”

This is the 22^nd post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.