Testing my Java solution with Excel

I thought I'd share a program I wrote in Java to solve a simple problem. It sums the multiples of three from 1 to 100. In other words, it loops through the numbers 1 to 100, and if it finds a number that is a multiple of three, it adds the number to the total.

import java.util.*;
/*
class to sum the multiples of 3 between 1 and 100
by Jimmy Pena, 12/4/08
*/
public class My_Program
{
// constant variable for multiple number
  static final int multiple = 3;

  public static void main(String[] args)
  {
    int loop_counter;
    int sum = 0;

    for (loop_counter = 1; loop_counter <= 100; loop_counter++)
    {
	 // modulo will return zero if the number is a multiple of 3
      if (loop_counter % multiple == 0)
    	   sum = sum + loop_counter;
    }
	 if (sum > 0)
    System.out.println("The sum of all multiples of 3 between 1 and 100 is " + sum);
  }
}

I used Excel to test out the results of my program, to verify the expected output.

I know that the following array formula will produce a count:

{=SUM(IF(,1))}

This formula will add one to the sum total, wherever the condition in the if statement is met. (It's really a sum, but since we're only adding one, it's effectively a count.) For example, if I wanted to know how many times the number 100 appeared in the range A1:G100, I would use the following:

{=SUM(IF(A1:G100=100,1))}

I also know that the ROW() function will return an array of numbers, and the MOD() function is what is used to check if a number is divisible by another number. If a given number is divisible by three (i.e. it's a multiple of 3), the remainder will be zero. In other words, x % y or mod(x,y) will be zero if x is a multiple of y.

Therefore, the following formula will return a count of the number of numbers between 1 and 100 that are multiples of three:

{=SUM(IF(MOD(ROW($1:$100),3)=0,1))}

Now if I want to actually return the sum, it's just a small modification of the formula:

{=SUM(IF(MOD(ROW($1:$100),3)=0,ROW($1:$100)))}

This function returns the row number, which is what gets tested in the first part of the function. Instead of adding one, it adds the matching row number.

So the ROW() function creates an array of numbers from 1 to 100 like so: {1;2;3;4;5…} and so on. The semicolon means the numbers are in different "virtual" rows (thanks to the ROW() function).

rowarray

The MOD() function checks each one to see if it is a multiple of 3 (by checking if the remainder is zero) and if so, it returns the remainder. The resulting array looks something like this:

{1;2;0;1;2;0;1;2;0} etc.

mod result

These are the remainders of 1 mod 3, 2 mod 3, 3 mod 3, 4 mod 3, and so on.

Each of these are compared to zero (the '=0' part of the formula) and an array of TRUE and FALSE values are created. Then the second ROW($1:$100) is evaluated, and an array of numbers from 1 to 100 are returned (same as above). The 100 TRUE and FALSE values are compared against the 100 numbers, which leaves you with something like this:

{FALSE;FALSE;3;FALSE;FALSE;6;FALSE;FALSE;9;FALSE;FALSE;12;}

tfarray