POWER FORMULA TECHNIQUES

Created by David Hager

The problem – to make a 3D formula that adjusts in a z-relative manner
when it is filled across worksheets. Two separate solutions to this problem
that use a similar methodology are shown below.

Making a 3D Running Total –

This example uses information entered in column A, with the 3D Running Total
formula in column B.

Define shtPos as:

This formula returns the sheet position of the active sheet as an integer.

=GET.DOCUMENT(87)

Define wsNames as:

This formula returns an array of sheet names in the active workbook.

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND(“]”,GET.WORKBOOK(1)))

Define shtName as:

This formula returns the active sheet name.

=INDEX(wsNames,shtPos)

Note: The string returned from GET.DOCUMENT(76) could have been modified
to obtain the active sheet name, but the method shown above was used since
the modified array of sheet names was available (and needed for another
3D example).

Define RunningTotal as:

=EVALUATE(“SUM(Sheet1:”&shtName&”!A”&ROW()&”)”)+NOW()*0

This formula returns the sum for the cell in column A for each worksheet
from Sheet1 to the worksheet where the formula resides. For example, if
the active sheet was Sheet4 and this formula was in B2, this formula
(entered as =RunningTotal) would be the equivalent of the Excel formula
=SUM(Sheet1:Sheet4!A2). Since this formula incorporates xlm macro functions,
it must be forced to recalculate. This is done by using the term NOW()*0,
since NOW() is a volatile function.

Making a 3D Moving Average –

Define shtNamem2 as:

=INDEX(wsNames,shtPos-2)

This formula returns the sheet name of the worksheet 2 tabs to the left of
the active sheet.

Define MovingAverage as:

=EVALUATE(“AVERAGE(“&shtNamem2&”:”&shtName&”!A”&ROW()&”)”)+NOW()*0

This formula returns the average of the values in the sheet 2 tabs to the
left of the active sheet to the active sheet. For example, if the active
sheet was Sheet4 and this formula (entered as =MovingAverage) was in B2,
this formula would be the equivalent of the Excel formula =AVERAGE(Sheet2
:Sheet4!A2).

Editor’s Note: Here’s the first method in action:

Same setup, but for the second method: