# Issue No. 03 (April 15, 1999)

********************************** COMMENTS Welcome to the third issue of the Excel Experts E-letter (or EEE), by David Hager. EEE is a semi-monthly publication. Feel free to distribute copies of EEE to your friends and colleagues. In honor of this being the 3rd issue of EEE, I have decided to dedicate this issue to 3D solutions. As you might know, the 3D capabilities of Excel are lacking in several areas. However, this just provides fertile ground for growing those workarounds. ********************************** TOP EXCEL WEB SITES For the best in 3D add-in functionality, check out: http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm Also, check out the freeware file Make Excel 3D in the CompuServe Excel forum library for a comprehensive set of 3D custom functions. ********************************** WORKSHEET FORMULA TIPS Excel allows you to make 3D formulas based on the following syntax: Sheet1:Sheet4!A2:B5 However, the functions that can actually use that syntax are limited (although not as limited as the Excel documentation would lead you to believe). I put together the following list of functions that represent most, but probably not all, of those that are 3D-enabled: AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA, SMALL, LARGE, RANK, MEDIAN, PERCENTILE, QUARTILE, TRIMMEAN, SKEW, AND, OR, AVEDEV, DEVSQ, SUMSQ BTW, If you create a defined name for this type of 3D range, be sure to group all of the sheets in that range if you decide to insert any rows into the area bounded by that range (if you want your 3D range to reflect the insertion of that row). Created by David Hager To make a conditional format based on the value in the previous worksheet, create the following defined name formulas. GlobRef as: =INDIRECT("rc",FALSE) which gives the value from the cell it is used in. PrevShtValue as: =INDIRECT(INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&"!"&ADDRESS(ROW(), COLUMN())) which gives the value from the cell of the same address in the previous sheet. Then, combine these in yet another defined name formula. GTPSV (this cell value is greater than previous sheet value) as: =GlobRef>PrevShtValue which is used as the conditional formatting formula (in Excel 97 and later versions). ********************************** 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). Created by Laurent Longre The problem - to make a 3D worksheet array formula. What this means is to create an array representing a z-range (a range across worksheets) that evaluates in the formula bar as an array. The 3D range used in Excel, i.e. Sheet1:Sheet4!A2:B5 does not behave that way. I suspect that nearly everyone on the EEE list has tried to do this and found that it was not possible. However, Laurent found that it was possible, given some advanced formula tricks. The INDIRECT function can return a 3D reference if it is operated on by the N function. An illustration of this type of formula is shown below. 3D Diagonal Formula - =SUM(N(INDIRECT("Sheet"&{1,2,3}&"!"&ADDRESS({1,2,3},{1,2,3})))) returns the sum of Sheet1!A1, Sheet2!B2 and Sheet3!C3. How does it work? "Sheet"&{1,2,3}&"!"&ADDRESS({1,2,3},{1,2,3}) evaluates to the array of strings. {"Sheet1!$A$1","Sheet2!$B$2","Sheet3!$C$3"} When the INDIRECT function operates on this array, the expected array of values appear (by highlighting in the formula bar and pressing F9), but for some reason this array cannot be used by Excel functions. The use of the N function creates an array that can be used, so that the SUM function returns the desired result. ********************************** POWER PROGRAMMING TECHNIQUES By Myrna Larson and David Hager Presented below are 3 UDF's (SumProduct3D, SumIf3D, CountIf3D) that provide an useful method of returning a variety of information from 3D ranges. Each of these functions use a 3D range argument (written as per the usual Excel protocol) as a string. This string is processed by the Parse3DRange function, which returns sheet positions and the range argument in variables that are used by these functions. Function SumProduct3D(Range3D As String, Range_B As Range) _ As Variant Dim sRangeA As String Dim sRangeB As String Dim Sheet1 As Integer Dim Sheet2 As Integer Dim Sum As Double Dim n As Integer Application.Volatile If Parse3DRange(Application.Caller.Parent.Parent.Name, _ Range3D, Sheet1, Sheet2, sRangeA) = False Then SumProduct3D = CVErr(xlErrRef) Exit Function End If sRangeB = Range_B.Address Sum = 0 For n = Sheet1 To Sheet2 With Worksheets(n) Sum = Sum + Application.WorksheetFunction.SumProduct( _ .Range(sRangeA), .Range(sRangeB)) End With Next SumProduct3D = Sum End Function '~~~~~~~~~~ Function SumIf3D(Range3D As String, Criteria As String, _ Optional Sum_Range As Variant) As Variant Dim sTestRange As String Dim sSumRange As String Dim Sheet1 As Integer Dim Sheet2 As Integer Dim n As Integer Dim Sum As Double Application.Volatile If Parse3DRange(Application.Caller.Parent.Parent.Name, _ Range3D, Sheet1, Sheet2, sTestRange) = False Then SumIf3D = CVErr(xlErrRef) End If If IsMissing(Sum_Range) Then sSumRange = sTestRange Else sSumRange = Sum_Range.Address End If Sum = 0 For n = Sheet1 To Sheet2 With Worksheets(n) Sum = Sum + Application.WorksheetFunction.SumIf(.Range _ (sTestRange), Criteria, .Range(sSumRange)) End With Next n SumIf3D = Sum End Function '~~~~~~~~~~ Function CountIf3D(Range3D As String, Criteria As String) _ As Variant Dim Sheet1 As Integer Dim Sheet2 As Integer Dim sTestRange As String Dim n As Integer Dim Count As Long Application.Volatile If Parse3DRange(Application.Caller.Parent.Parent.Name, _ Range3D, Sheet1, Sheet2, sTestRange) = False Then CountIf3D = CVErr(xlErrRef) Exit Function End If Count = 0 For n = Sheet1 To Sheet2 With Worksheets(n) Count = Count + Application.WorksheetFunction.CountIf( _ .Range(sTestRange), Criteria) End With Next n CountIf3D = Count End Function '~~~~~~~~~~ Function Parse3DRange(sBook As String, SheetsAndRange _ As String, FirstSheet As Integer, LastSheet As Integer, _ sRange As String) As Boolean Dim sTemp As String Dim i As Integer Dim Sheet1 As String Dim Sheet2 As String Parse3DRange = False On Error GoTo Parse3DRangeError sTemp = SheetsAndRange i = InStr(sTemp, "!") If i = 0 Then Exit Function 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i > 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With Workbooks(sBook) FirstSheet = .Worksheets(Sheet1).Index LastSheet = .Worksheets(Sheet2).Index 'swap if out of order If FirstSheet > LastSheet Then i = FirstSheet FirstSheet = LastSheet LastSheet = i End If i = .Worksheets.Count If FirstSheet >= 1 And LastSheet <= i Then Parse3DRange = True End If End With Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange ********************************** EXCEL 2000 INFORMATION Although the ability to manipulate data with worksheet formulas did not change in Excel 2000, the other data handling features in Excel 2000 more than made up for this. In fact, IMHO, it is now ready to take on high-level data intensive corporate projects that previously could only be accomplished with a variety of tools. Among these are interactive web pages, Office Web Components, Pivot Tables and Charts, web queries, a greatly enhanced QueryTable object model, and OLAP cube technology. I plan to provide examples ofeach of these in upcoming issues (with a little help from the readership, I hope ). ********************************** Issue No.3 OF EEE (PUBLISHED 15/16Apr1999) Next issue scheduled for 01MAY1999. BY David Hager dchager@compuserve.com

Excel Expert Newsletter Archives

Here you'll find the archives of David Hager's *Excel Expert's E-Letter*, produced in 1999-2001. This information is
old and unorganized, but it's here because it still contains lots of useful information. The newsletters contains quite a few
links. Needless to say, most are no longer valid.

It's interesting to note that some of the key problems back then are still key problems today.