It just needs an OFFSET

Running through numbers in a spreadsheet again, and a problem to solve with it.

Suppose you have a set of measurements for several days, one per hour. That means 24 samples per day (guessed that?): so far, so good.

Getting the maximum for each day is not difficult either. For example, one could select the cell at the right of the first sample, say D2, and input something like "=MAX(C2:C25)" to get the maximum for the first day of data. That's simple, too. And if one wants to do that for each day, he can select the cells D2:D25, and paste them down from cell D26 on.

Now, and that was my problem, what if you want to make a new sheet where, in a column, you have only those maximums, one after the other? Of course, you could just make, say, cell B2 as "=D2", cell B3 as "=D26" and so forth, but that's not quite practical. I needed a way to make the row number after D parametric, so that I could say that B(i)=D(24*(i-2)+2) — just try with a few values for i, and you'll see it works. Getting the right value for that "i" is easy, there's the ROW function for that, but… how can one use it as "the row number after D"?

Well, after some research, I found out that there is a way, and it's a function called "OFFSET":

Returns the value of a cell offset by a certain number of rows and columns from a given reference point.

OFFSET(Reference; Rows; Columns; Height; Width)

So, to make B2=D2, B3=D26 and so forth? Just use "=OFFSET($D$2;(24*(ROW()-2));0)", and then drag it down to the desired cell. Done!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.