### 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.

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. |

#### 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.

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