Incrementing Relative References in Formulas by More Than One

 Last reviewed: February 2, 1998
Article ID: Q152265
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

When you fill formulas down a column or across a row, the relative references are, by default, incremented by one. The formulas can be altered so that they are incremented by a user-defined amount through the use of the OFFSET function.

MORE INFORMATION

The formula for incrementing by more than a row is: 

   =OFFSET(<Starting_Cell>,(ROW()-<Current_Row>)*<Inc>,0)
where <Starting_Cell> is the absolute reference to the first cell in the range of interest, <Current_Row> is the row of the cell in which the formula is being entered, and <Inc> is the number of rows to increment.

The formula for incrementing by more than column is: 

   =OFFSET(<Starting_Cell>,0,(COLUMN()-<Current_Column>)*<Inc>)
where <Starting_Cell> is the absolute reference to the first cell in the range of interest, <Current_Column> is the number associated with the column of the cell in which the formula is being entered, and <Inc> is the number of columns to increment.

Example Incrementing a Formula by More Than One Row

  1. Type the following information in a worksheet: 

          A1: Patrick    B1:
      A2: 79         B2:
      A3: Ricky      B3:
      A4: 68         B4:
      A5: Matt       B5:
      A6: 23         B6:
      A7: John       B7:
      A8: 15         B8:
      A9: Mary       B9:
     A10: 40        B10:

  2. Type the following formula in cell B1: 

          B1: =OFFSET($A$2,(ROW()-1)*2,0)

  3. With cell B1 selected, grab the fill handle and fill down the formula through cell B5. The resulting cells will look as follows: 

          B1: 79
      B2: 68
      B3: 23
      B4: 15
      B5: 40

Example Incrementing a Formula by More Than One Column

  1. Type the following information in a worksheet: 

          A1: Patrick   B1: 79   C1: Ricky   D1: 68   E1: Matt   F1: 23
      A2:           B2:      C2:         D2:      E2:        F2:

  2. Type the following formula in cell A2: 

          A2: =OFFSET($B$1,0,(COLUMN()-1)*2)

  3. With cell A2 selected, grab the fill handle and fill the formula right through cell C2: 

          A2: 79  B2: 68  C2: 23

Example Using ISEVEN Function

This formula is not restricted to extracting data. It can also be used within other formulas. By using the following formula, you can test if the numerical data in every fourth row is even: 

   =IF(ISEVEN(OFFSET(<Starting_Cell>,(ROW()-<Current_Row>)*<Inc>,0),
      "EVEN","ODD")
where <Starting_Cell> is the absolute reference to the first cell in the range of interest, <Current_Row> is the row of the cell in which the formula is being entered, and <Inc> is the number of rows to increment.

NOTE: This formula requires that the Analysis ToolPack is included in the Add-Ins. To do this, on the Tools menu, click Add-Ins and click to select the check in the box next to Analysis ToolPack.

  1. Type the following information in a worksheet: 

          A1: Patrick    B1:
      A2: 79         B2:
      A3: Ricky      B3:
      A4: 68         B4:
      A5: Matt       B5:
      A6: 23         B6:
      A7: John       B7:
      A8: 15         B8:
      A9: Mary       B9:
     A10: 40        B10:

  2. Type the following formula in cell B1: 

          B1: =IF(ISEVEN(OFFSET($A$2,(ROW()-1)*4,0)),"EVEN","ODD")

  3. With cell B1 selected, grab the fill handle and fill down the formula through cell B3. The resulting cells will look as follows: 

          B1: ODD
      B2: ODD
      B3: EVEN

For additional information, please see the following article(s) in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q151337
   TITLE     : Incrementing Relative References by More Than One Cell in
               VBA

Additional query words: 5.00 5.00c 7.00 7.00a 5.00a 97 8.00 98 XL98 XL97
XL7 XL5 howto database mailing labels mail nth multiple multiples
Keywords : xlformula kbualink97
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.