BET: Microsoft Excel Formulas

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Formulas > 3D Formulas

< Previous | Next >

What is a 3D Formula ?

	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.

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.

AND	The logical AND for any number of arguments.
AVEDEV	The average deviation for a list of numbers.
AVERAGE	The arithmetic mean of a list or array of numbers.
AVERAGEA	The arithmetic mean of a list or array of numbers, including text and logical values.
COUNT	The number of cells with a numeric value in a list or cell range.
COUNTA	The number of non blank cells in a list or cell range.
DEVSQ	The sum of squares of deviations of data points from their sample mean.
LARGE	The Kth largest value in an array of numbers.
MAX	The largest value in a list or array of numbers.
MAXA	The largest value in a list or array of numbers, including text and logical values.
MEDIAN	The median of the numbers in a list or cell range.
MIN	The smallest number in a list or range.
MINA	The smallest number in a list or range, including text and logical values
OR	The logical OR for any number of arguments.
PERCENTILE	The Kth percentile of values in a range.
PRODUCT	The product of all the numbers in a list or cell range.
QUARTILE	The quartile of a data set.
RANK	The rank of a value in a range (in descending order).
SKEW	The number representing the skewness of a distribution.
SMALL	The Kth smallest value in an array of numbers.
STDEV	The standard deviation based on a sample.
STDEVA	The standard deviation based on a sample, including text and logical values.
STDEVP	The standard deviation based on an entire population.
STDEVPA	The standard deviation based on an entire population, including text and logical values.
SUM	The total value of the numbers in a list or cell range.
SUMSQ	The sum of the squares of all the values in a list or cell range.
TRIM	The text string with all spaces removed from the beginning and end.
VAR	The compound variance based upon the numerical values in the range.
VARA	The compound variance based upon the numerical values in the range.
VARP	The variance based on an entire population.
VARPA	The variance based on an entire population, including text and logical values.

Things to Remember

		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.

< Previous | Top | Next >

Leading the way in Microsoft Office Development

What is a 3D Formula ?

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.

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.

Things to Remember

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.