3D Formulas

These are also known as cubed formulas.
A reference that refers to the same cell or range on multiple worksheets is called a 3D reference.
Using 3D formulas allows you to calculate data thorughout a workbook using multiple worksheets.
All 3D formulas are based on the syntax: Sheet1:Sheet4!A2:B5.
A 3D formula is a formula that refers to the same cell (or range of cells) on multiple worksheets.
The 3D formula "=SUM(Sheet1:Sheet4!A2)" can be used to add up the numbers in cell "A2" on 4 different worksheets.
If you copy or insert a new worksheet after Sheet1 the reference will automatically include it.
Similarly if you delete a worksheet it will be excluded.


If you copy or insert a new worksheet after Sheet1 the reference will automatically include it.
Similarly if you delete a worksheet it will be excluded.


Summarising your worksheets

Lets assume that we have a workbook that contains five worksheets and that four of them contain data for specific years.
Four of the worksheets correspond to the sales figures for the years 2005 - 2002 and the first worksheet is intended to be a summary of these four years.

On the Summary worksheet we want to be able to quickly return the total for all the Regions and for the months.
It is possible to create 3D formulas which refer to all four worksheets which makes creating a summary worksheet very easy.
Lets assume that each of the four worksheets contains the following table of data.


Inserting the Formula

We are going to insert a 3D formula for each of the items we want to total.
The first item in our summary table is the total for Region 1.
Select cell "C2" and insert the SUM function as normal.

Select the "2005" worksheet tab with the mouse.
Hold down the Shift key and select the "2002" worksheet tab with the mouse.

Select the cell you want to sum in this case cell "G3".
Enter a close bracket ")" to complete the formula.

Press Enter to return to the Summary worksheet.
Create the following table on the Summary worksheet.

Repeat the above steps for the other six totals to create your summary worksheet.


Worksheet Functions

It is important to realise that not all the functions will accept a 3D formula.
The following functions can all be used in 3D formulas.

ANDThe logical AND for any number of arguments.
AVEDEVThe average deviation for a list of numbers.
AVERAGEThe arithmetic mean of a list or array of numbers.
AVERAGEAThe arithmetic mean of a list or array of numbers, including text and logical values.
COUNTThe number of cells with a numeric value in a list or cell range.
COUNTAThe number of non blank cells in a list or cell range.
DEVSQThe sum of squares of deviations of data points from their sample mean.
LARGEThe Kth largest value in an array of numbers.
MAXThe largest value in a list or array of numbers.
MAXAThe largest value in a list or array of numbers, including text and logical values.
MEDIANThe median of the numbers in a list or cell range.
MINThe smallest number in a list or range.
MINAThe smallest number in a list or range, including text and logical values
ORThe logical OR for any number of arguments.
PERCENTILEThe Kth percentile of values in a range.
PRODUCTThe product of all the numbers in a list or cell range.
QUARTILEThe quartile of a data set.
RANKThe rank of a value in a range (in descending order).
SKEWThe number representing the skewness of a distribution.
SMALLThe Kth smallest value in an array of numbers.
STDEVThe standard deviation based on a sample.
STDEVAThe standard deviation based on a sample, including text and logical values.
STDEVPThe standard deviation based on an entire population.
STDEVPAThe standard deviation based on an entire population, including text and logical values.
SUMThe total value of the numbers in a list or cell range.
SUMSQThe sum of the squares of all the values in a list or cell range.
TRIMThe text string with all spaces removed from the beginning and end.
VARThe compound variance based upon the numerical values in the range.
VARAThe compound variance based upon the numerical values in the range.
VARPThe variance based on an entire population.
VARPAThe variance based on an entire population, including text and logical values.

Important

It is possible to create named ranges for 3D cell references which can be used to simplify your 3D formulas.
For more information on 3D named ranges, please refer to the 3D Named Ranges page.


© 2018 Better Solutions Limited. All Rights Reserved. © 2018 Better Solutions Limited TopPrevNext