Excel Essentials: What's Up With The Row Function?

At first glance the ROW function seems rather ridiculous.  It only takes one argument and simply returns the row number of the referenced cell.  For example, ROW(A1) returns the number 1.  Seems useless, right?  But when thought of as a way to increment a variable in an algebraic formula, it suddenly becomes quite useful.  Let's explore three different ways to use the ROW function.

Omitting Values

In this example, let's assume that we're collecting rain water.  A measurement is taken in milliliters every minute over the course of twenty minutes, but we only need to extract the measurement of water every four minutes.  We could extract every fourth measurement by hand but if we had hours or days worth of data, that would be a daunting task.  Here's my favorite way to do it using the ROW and the INDEX functions.

The first argument of the INDEX function is the range that contains our measurements.  By using the ROW function in the second argument  of the INDEX function, we can increment our formula every time we copy it down.

If we look at the second argument of the INDEX function algebraically, the formula breaks down to 1*4-3 which is equal to 1.  Once the formula is copied down one row, the formula becomes 2*4-3 which is equal to 5.  This is how the INDEX function returns every fourth position in the range (B1,B5,B9,etc.).

Repeating Values

Now let's use the ROW function to achieve the opposite effect.  Suppose we need to perform four different tests to each sample of water every minute.  Rather than displaying every fourth measurement we need to display every measurement four times.

We can repeat each value in the measurements range any number of times by pairing the INT function with the ROW function.  The INT function returns an integer so this formula again comes down to algebra.  Note that in the previous example we used A1 as the argument for the ROW function, but now we're using A4.  Here's why:

INT(ROW(A4)/4) basically says "Divide four by four and truncate the result so that it's an integer."  The solution to the formula will be 1 until the fifth iteration when the formula becomes INT(ROW(A8)/4) and the solution becomes 2.

Summing Intervals

Lastly, suppose we want to take the sum of every four minute interval.

We can get the sum of every four minutes by inserting the same formula from the first example, along with a slight variation of itself, into the SUM function.  Notice that the only difference between the two INDEX formulas is that the -3 is omitted from the second formula.

Now you can see how the ROW function can be a powerful tool to help make your formulas more dynamic.