You are currently offline, waiting for your internet to reconnect

Incrementing Relative References in Formulas by More Than One

This article was previously published under Q152265
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
When you fill formulas down a column or across a row, the relativereferences are, by default, incremented by one. The formulas can bealtered so that they are incremented by a user-defined amount through theuse 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 therange of interest, <Current_Row> is the row of the cell in which theformula 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 therange of interest, <Current_Column> is the number associated with thecolumn of the cell in which the formula is being entered, and <Inc> is thenumber 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 usedwithin other formulas. By using the following formula, you can test if thenumerical 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 therange of interest, <Current_Row> is the row of the cell in which theformula is being entered, and <Inc> is the number of rows to increment.

NOTE: This formula requires that the Analysis ToolPack is included in theAdd-Ins. To do this, on the Tools menu, click Add-Ins and click to selectthe 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 theMicrosoft Knowledge Base:
151337Incrementing Relative References by More Than One Cell in VBA
5.00c 7.00a 5.00a 97 8.00 98 XL98 XL97 XL7 XL5 howto database mailing labels mail nth multiple multiples XL
Properties

Article ID: 152265 - Last Review: 08/17/2005 18:18:32 - Revision: 1.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • Microsoft Excel 95a
  • Microsoft Excel 5.0c
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 5.0 for Macintosh
  • Microsoft Excel 5.0a for Macintosh
  • kbhowto kbualink97 KB152265
Feedback