14 Oct 2015

Video 00139 Beyond 3D Formulas

Excel’s 3D formulas allow you to work with values from the same range in different sheets. However, not all Excel functions work with 3D formulas. Read my post to see how they work, how to go beyond 3D formulas and how to often avoid complex formulas with proper spreadsheet design!

Review of Excel’s Nifty 3D Range Formula

Don’t forget that you can change the COUNT function to others functions like AVERAGE or SUM.

Below, you’ll see the functions that are compatible with 3D ranges.

3D Range Compatible Functions

Once you understand the structure of how 3D formulas work (watch video below) it’s important to know which Excel functions are compatible with the 3D range functionality.

Here is a list of compatible Excel functions:

AND  AVEDEV  AVERAGE  AVERAGEA  COUNT  COUNTA  DEVSQ  LARGE  MAX  MAXA  MEDIAN  MIN  MINA  OR  PERCENTILE  PRODUCT  QUARTILE  RANK  SKEW  SMALL  STDEV  STDEVA  STDEVP  STDEVPA  SUM  SUMSQ  TRIM  VAR  VARA  VARP  VARPA

Here are some examples:

• =COUNT(‘Month 1:Month 4’!B6:B11)
• =SUM(‘Month 1:Month 4’!B6:B11)
• =AVERAGE(‘Month 1:Month 4’!B6:B11)

What About Countif, Countifs, Sumif, Sumifs, etc?

Functions that are not listed above in blue are not compatible with 3D range formulas. However, it’s possible to work with data across sheets with complex formulas.

Here are some examples:

• =SUMPRODUCT(COUNTIF(INDIRECT(“‘”&SheetList&”‘!B6:B11″),”>”&G7))
• =SUMPRODUCT(SUMIF(INDIRECT(“‘”&SheetList&”‘!B6:B11″),”>”&G7))
• =SUMPRODUCT(COUNTIFS(INDIRECT(“‘”&SheetList&”‘!a6:a14″),”=”&”Sales”,INDIRECT(“‘”&SheetList&”‘!B6:B14″),”>”&G7))

Is There an Easier Method?

You can often avoid a lot of complex formulas if you spend more time up front designing your spreadsheet. Download my Excel file and go to sheet ‘In a perfect world!’. If all the fields are in separate columns in only 1 sheet then it’s much easier to analyze your data with pivots and formulas. This is ideally how data should be stored in a spreadsheet.

When a single data set is chopped up and spread out in different sheets then complex formulas are often required to pull things back together. So, the choice is yours…you can get started quickly and pay the cost later or spend additional time up front designing the layout of your model and save time in the long run. Yes, it’s definitely possible to have different sheets of data but ideally the sheets should be independent data sets.

• While writing this post I noticed that Hui over at Chandoo.org wrote a post about finding the max or min value across sheets. There are also some comments below the post with alternative solutions that don’t use the volatile Indirect function.