Another Java solution in Excel

In Java I came upon the following problem:

Loop through the numbers 1 to 100 and print out numbers whose square root to the power of 2 equals itself.

import static java.lang.Math.*;

public static TestProgram
{
  public static void main(String[] args)
  {
    int count;

    for (count = 1; count &lt;= 100; count++)
      if (pow(floor(sqrt(count)), 2) == count)
        System.out.print(count + &quot; &quot;);

    System.out.println();
  }
}

I leave the actual answer as an exercise for the reader (or you can just open the Excel workbook sample from the link below). As usual, I used Excel to verify the Java solution. Maybe I trust Excel more than the JVM?

I'll need a way to check the numbers 1 through 100 and see if the square root of each one, multiplied by itself, equals the original number. I'll use a technique similar to what I did last time: fill down a formula and use the ROW() function to increment the number in question.

The Java formula is: if (pow(floor(sqrt(count)), 2) == count) which roughly translates to "If the square root of a loop counter value, rounded down and raised to the power of two, equals itself, then print it." We'll try to translate this directly to an Excel formula. I know the ROW() function returns a number corresponding to the row, so if we are checking numbers 1 through 100, the formula will need to be filled down from rows 1 to 100 in any column.

Fortunately, most of these functions are directly usable in Excel: there's a POWER (not POW), FLOOR and SQRT functions that all mean and do the same thing in Excel as in Java.

I will need to tweak the formulas a bit, however, since they work differently in Excel than in Java. For example, the FLOOR formula in Excel requires a second parameter (significance), whereas Java just "knows" where to round. Here's the final formula:

=IF(POWER(FLOOR(SQRT(ROW()),1),2)=ROW(),ROW(),"")

This formula is filled down from rows 1 to 100. In each formula, the ROW() function will be replaced with the numbers 1,2,3,4,5 … etc. So in row 1, the formula will be

=IF(POWER(FLOOR(SQRT(1),1),2)=1,1,"")

The end result will be a column displaying all numbers from 1 to 100 that meet our criteria above. Lucky for me, the answers matched!

Download sample workbook