offsets in excel

Topic

New Reply

I don’t know why I suck so much at using Offset. Maybe somebody has a primer. It’s just some lack of intuitive common sense about the math involved.

For instance, how do I set up an offset type reference call to “Fill Down” in the formula that references a range of 3 cells going down.
I know I have to use offset and row() and some magic combination of pluses and minuses but I can never figure it out without resorting to brute force trial and error. Help me learn! Thanks!!

The db is set up like this:

Sample Value
A-Trial1 10
A-Trial2 9
A-Trial3 9.5
B-Trial1 11
B-Trial2 12
B-Trial3 10
etc…

Sample Average
A =average(B2:B4)
B =average(B5:B7)
etc…

Reply | Quote

Replies

I’m not totally sure of all you’re trying to do but let me assume you have a col of numbers in col B with a col heading in row 1, and you want the average of groups of 3 rows of numbers as you showed (average of B2:B4, B5:B7, etc.). What you didn’t really say is where you wanted to store the results. So I’m going to assume starting in D1 (the column doesn’t matter but the row does).

I’m going to use indirect and, as you said row.

The key thing is to get a formula for the rows – relate the rows where you’re going to store the average to the first row of the 3 that will go into the average (that’s why I said above that the row for storing the averages does matter). As to the last row of the 3 that goes into the average, I’ll deal with that later.

So in row 1 (actually D1), you want the average of the 3 numbers beginning in row 2. In row 2 (actually D2), you want the average of the 3 numbers beginning in row 5. Make a table of this on scratch paper.
Row where storing avg 1st row of 3 numbers to be averaged
1 2
2 5
3 8

[Clarification on above table: the above is supposed to be a 2-col table, with the first col being “Row where storing avg” and the 2nd col being “1st row of 3 numbers to be averaged”. Not coming out the way I want since I used spaces to create the cols. the other 3 rows of the table each have 2 single-digit entries, 1 for each col.]

From the table you can see that the 1st row to be averaged changes by 3 as you go down the table. If there was a row 0 to be used for storing averages, using the pattern in the table above would mean that row 0 (or D0) would have the average of the numbers beginning at row -1 (go backwards in the table so instead of 2+3=5, 5+3=8, you do 2-3=-1). While both of these ideas (row 0 and row -1) are fictitious concepts in Excel, getting this info is important for the formula I’m going to develop.

The formula will be in the form
=average(‘something for the first of 3 rows’ : ‘something for last of 3 rows’)

Using what I did so far above for the beginning of the range – ie, the first of the 3 rows – gives
=average(indirect(“D” & 3*row()-1) ” ‘something for last of 3 rows’)
where D is the col I chose in which to store my averages starting at row 1, 3 is because you’re averaging 3 numbers together at once and the table above changes by 3 for the 1st number to be averaged, and -1 is that fictitious number that corresponds to the first number to be averaged when the average is stored in row 0. It is this number – ie, -1 – that will change depending on where you start storing your averages. As I said, I chose to start storing them in row 1 (D1).

As to the end of the range of things to be averaged, that’s easy. If the beginning of the range is as given above, then the last row number to be averaged is 2 rows higher (ie, if your first row to be averaged is row 2, then 3 rows averaged makes the last row to be averaged as row 4). So add 2 to row()-1 and you get row()+1.

The entire formula, stored in D1, becomes
=average( indirect(“D” & 3*row()-1) : indirect(“D” & 3*row()+1) )

You can fill starting from D1 so that D2 has the average of B5:B7

I’ve put in extra spacing in the formula to separate parts of the formula for clarity.

You could do the equivalent with OFFSET and ROW but I think in terms of indirect for problems like these.

HTH. (If not, I hope others will chip in since I’m leaving for a long weekend tomorrow.)

Fred

Reply | Quote

Hey Fred,

Indirect?!?! Oh no! Yet another way to do this!

I’ll have to study your reply more carefully. I didn’t “see” the way from a first pass though I did the usual – adapt a google answer somewhere to retrofit to my problem and that worked (though I didn’t understand exactly how it worked).

Thanks for the help!
-Alex

Reply | Quote

Delenca,

Here’s what I do when I want to see the range (matrix, array, block, …) of cells referred to by OFFSET:

Insert > Name > Define

brings up the Define Name dialogue, with 3 boxes. Type a name into the first (temp seems a good choice!), then in the bottom box type the OFFSET function you want to “see”, for example:

=OFFSET($F$6,2,-1,6,3)

Click Add. At the end of the Refers to box there is an icon that looks like a grid. Click on this, the Define Name dialogue will shrink, and the range of cells will be outlined, so you can “see” the result of the OFFSET function. By ringing the changes, you should soon come to master this very useful function for defining an array of cells. For the example above:

$F$6 –> anchor
2 –> shift down 2 rows (takes you to $F$8)
-1 –> shift right -1 column (takes you to $E$8)

that’s defined the top left corner of the range, the remaining 2 parameters give the height and width of the range

6 –> height (rows 8 to 13)
3 –> width (columns E to G)

so OFFSET($F$6,2,-1,6,3) returns the range with $E$8 as the top left cell, and $G$13 as the bottom right cell.

Richard (from Piracicaba)

Reply | Quote