Using OFFSET to reference hyperblocks

This topic describes the OFFSET function. It refers to sample report RP006 in the Samples application.

The OFFSET function is not specific to Application Studio but is particularly useful when working with hyperblocks. This is because, in View mode, hyperblocks are dynamic. In Design mode, a hyperblock is static. That is, it occupies a specific range of cells. But in View mode, a hyperblock expands and contracts with its contents, making specific cell references meaningless. So, if, for example, you have a formula outside a hyperblock which must reference cells within the hyperblock, you need a dynamic formula.

With OFFSET you identify cells by specifying their position relative to an initial reference cell. You specify their position with coordinates that represent distance, in terms of the number of rows and columns from the from the initial reference cell.

This example references cell C13:

=OFFSET(F11,2,-3)

That is, cell F11 is the initial reference cell. The second argument, 2, indicates that the referenced cell is two rows below F11. The third argument, 3, indicates that the referenced cell is 3 columns to the left of F11.

This example also references cell C13:

=OFFSET(B21,-8,1)

That is, cell B21 is the initial reference cell. The second argument, -8 indicates that the referenced cell is 8 rows above B21. The third argument, 1, indicates that the referenced cell is 1 column to the right of B21.

A common use of OFFSET in Application Studio is in calculating cumulative values for use in, for example, ABC Analysis or Pareto charts. To calculate a cumulative value you add the value of the current cell to the value of the cell above. But in the dynamic environment of a hyperblock, the cell above cannot be specified with a static cell reference.

Report RP006 has an example of how to use of OFFSET to calculate cumulative values.

Report RP006 has a hyperblock, created from the Product dimension of the Analysis cube of the Samples application.

The hyperblock has two value cells. In the first value cell, an ROC formula displays a value for each group of products. In the second value cell is an OFFSET formula.

In View mode, the cell duplicates the values in the first value cell. This is because the OFFSET formula is incorrect.

The formula in cell E5 is:

=OFFSET(E5,0,0)+D5

To change the OFFSET formula so that is displays cumulative values:

  1. In Design mode, click cell E5.
  2. In the Formula Editor, change the second argument of the OFFSET formula from 0 to -1. That is, change the formula to: =OFFSET(E5,-1,0)+D5 Here, -1 specifies 'the row above'.

    In View mode, cumulative values are displayed and the label Cumulative is displayed. This is also achieved by using an OFFSET function in cell E3 - nested within an IF statement.

    The IF statement is: =IF(OFFSET(E3,3,0)=OFFSET(E3,3,-1),"","Cumulative")

    We know that the values in the first row of the results are always equal. But, if cumulative values are displayed, the values in all other rows are not equal. So, the IF statement tests whether the values in row 2 are equal.

    The IF statement translates as: If the value of the cell which is 3 rows below E3 equals the value in the cell which is 3 rows below and one column to the left of E3, then display nothing (""). Otherwise, display "Cumulative".