Statistical functions using 3D array with conditions/filters

[DaleW]

I have searched this forum for a while and cannot find how to make the following array formula work over multiple sheets. I do not know if it is even possible.

=MAX(IF(--($A3='FY18+ Reqs'!$B$2:$B$851000)*--("shipped"='FY18+ Reqs'!$F$2:$F$851000)*--(""<>'FY18+ Reqs'!$I$2:$I$851000),--('FY18+ Reqs'!$I$2:$I$851000-'FY18+ Reqs'!$G$2:$G$851000)))

I have been using it for a while and am currently using it as an array formula, Ctrl+Shift+Enter, but you can see I am almost out of rows on one Excel sheet. So I want to use it over more than one sheet for gathering statistics. I also use the other common statistic functions, MIN, AVERAGE, MODE, STDEV.S, SKEW, among others. I assume, if a 3D version is possible, the same methods will work for each of the other functions. I have seen examples using the INDIRECT function with the N() and T() functions. I have never been successful using the INDIRECT function. I have seen examples naming the ranges. No success so far.

A few descriptions from my formula:
$A3 is the identification number that is looked up in Column B of the requisition tab ; "shipped" is the requisition status looked up in Column F of the requisition tab ; ""<> filters out the empty ship dates in Column I of the requisition tab ; the last portion subtracts the requisition order date in Column G from the shipped date in Column I of the requisition tab.

 

[Domenic]

It's possible, but you'll find it very slow and inefficient. I would suggest that you first find the max for each individual sheet, and then find the overall max based on those individual maximums. If you'd like to try a single formula solution, first list your sheet names in a horizontal range of cells, let's say B1:D1. Then try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=MAX(IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!B2:B851000"),ROW(INDIRECT("2:851000"))-2,0,1))=$A3,IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!F2:F851000"),ROW(INDIRECT("2:851000"))-2,0,1))="shipped",IF(COUNTIF(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1),"<>")>0,N(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1))-N(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!G2:G851000"),ROW(INDIRECT("2:851000"))-2,0,1))))))

Adjust the range for the sheet names, accordingly.

Hope this helps!

 

[DaleW]

It's possible, but you'll find it very slow and inefficient. I would suggest that you first find the max for each individual sheet, and then find the overall max based on those individual maximums. If you'd like to try a single formula solution, first list your sheet names in a horizontal range of cells, let's say B1:D1. Then try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=MAX(IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!B2:B851000"),ROW(INDIRECT("2:851000"))-2,0,1))=$A3,IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!F2:F851000"),ROW(INDIRECT("2:851000"))-2,0,1))="shipped",IF(COUNTIF(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1),"<>")>0,N(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1))-N(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!G2:G851000"),ROW(INDIRECT("2:851000"))-2,0,1))))))

Adjust the range for the sheet names, accordingly.

Hope this helps!

Thank you Domenic! I got your formula to work. Much appreciated. I agree that finding the MAX, or MIN, for each individual sheet then finding the overall MAX, or MIN, from those would be faster. But that would not work for the AVERAGE function. The Average of Averages is not the same as the Average of the whole population. So I must use your formula and wait.

 

[Domenic]

Yes, that's very true. I guess with the amount of data you have for each sheet it's not possible to combine them into one sheet. In any case, I'm glad I was able to help. And thanks for your feedback.

Cheers!

 

[DaleW]

Yes, that's very true. I guess with the amount of data you have for each sheet it's not possible to combine them into one sheet. In any case, I'm glad I was able to help. And thanks for your feedback.

Cheers!

After further review, it appears the Average of Averages is the same, it does equal, the Average of the whole population. But the other common statistical functions, (e.g. STDEV.S) do not work this way.

However, now I would like to know if this 3D formula can be applied to the SLOPE function. Or perhaps the SLOPE function cannot be used in the 3D format but some other method. I believe all the data subsets would need to be combined together to form one data set then the SLOPE could be determined. The other issue is the SLOPE function requires Known X-values. I tried to concatenate the subsets without success. The SLOPE function is needed as it ignores blanks and LINEST does not.

Thanks.

 

[Domenic]

After further review, it appears the Average of Averages is the same, it does equal, the Average of the whole population.

Actually, I didn't think it would be so in all cases.

However, now I would like to know if this 3D formula can be applied to the SLOPE function.

Yes, the same type of formula can be used. But, again, it's going to be very slow and inefficient. Any way, assuming that B1:D1 contains your sheet names, and that the same conditions need to be met, and that Column I contains your known y values and Column J contains your known x values, try...

=SLOPE(IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!B2:B851000"),ROW(INDIRECT("2:851000"))-2,0,1))=$A3,IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!F2:F851000"),ROW(INDIRECT("2:851000"))-2,0,1))="shipped",IF(COUNTIF(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1),"<>")>0,N(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1))))),IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!B2:B851000"),ROW(INDIRECT("2:851000"))-2,0,1))=$A3,IF(T(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!F2:F851000"),ROW(INDIRECT("2:851000"))-2,0,1))="shipped",IF(COUNTIF(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!I2:I851000"),ROW(INDIRECT("2:851000"))-2,0,1),"<>")>0,N(OFFSET(INDIRECT("'"&$B$1:$D$1&"'!J2:J851000"),ROW(INDIRECT("2:851000"))-2,0,1))))))

The SLOPE function is needed as it ignores blanks and LINEST does not.

That's right, LINEST does not ignore blanks (or FALSE logical values).

 

[Eric W]

The Average of Averages will be the same as the average of the whole population IF AND ONLY IF the number of rows on each sheet is the same. If not, you could calculate it if you also save the number of rows per sheet, something like:

=(a1*c1 + a2*c2 + a3*c3 + a4*c4)/(c1+c2+c3+c4)

where a1 is the average from sheet1, and c1 is the count from sheet1, etc. Since you're filtering out certain rows, the counts would be the rows actually included.


Incidentally, the worksheet functions MAX, SLOPE, STDEV.S, etc. can all be called from a VBA macro, and they accept arrays over 1M items. It shouldn't be too hard to write something, either a callable macro or a UDF, that returns your multi-sheet formulas in an efficient manner.

 

[Domenic]

Eric, thanks for clarifying Average of Averages.

Cheers!

 

[DaleW]

Incidentally, the worksheet functions MAX, SLOPE, STDEV.S, etc. can all be called from a VBA macro, and they accept arrays over 1M items. It shouldn't be too hard to write something, either a callable macro or a UDF, that returns your multi-sheet formulas in an efficient manner.[/QUOTE]

Eric, thank you also for clarifying the Average of Averages. In my review I did use the same number of rows in each data subset.

If you would provide a UDF for worksheet functions MAX, SLOPE, STDEV.S, etc. I would very much appreciate it! I have had as much success with UDFs as I have had with the INDIRECT function, which is none. I used Excel VBA in the past but have not for several years. I assume this UDF could be stored in PERSONAL.xlsb?

Thank you!

 

[Eric W]

OK, save this UDF in a general module (and I think PERSONAL.xlsb should work):

Public Function My3DStats(func As String, shts As Range, col1 As String, parm1 As String, _
                col2 As String, parm2 As String, datacol1 As String, Optional datacol2 As String = "")
Dim MyDict1 As Object, MyDict2 As Object, w As Variant, ws As Worksheet, lr As Long, i As Long
Dim d1 As Variant, d2 As Variant, d3 As Variant, d4 As Variant, ctr As Long, r1 As Variant, r2 As Variant


    Set MyDict1 = CreateObject("Scripting.Dictionary")
    Set MyDict2 = CreateObject("Scripting.Dictionary")
    
    For Each w In shts
        Set ws = Sheets(CStr(w))
        lr = ws.Cells(Rows.Count, col1).End(xlUp).Row
        d1 = ws.Cells(2, col1).Resize(lr - 1).Value
        d2 = ws.Cells(2, col2).Resize(lr - 1).Value
        d3 = ws.Cells(2, datacol1).Resize(lr - 1).Value
        If datacol2 <> "" Then d4 = ws.Cells(2, datacol2).Resize(lr - 1).Value
        For i = 1 To lr - 1
            If LCase(d1(i, 1)) = LCase(parm1) And LCase(d2(i, 1)) = LCase(parm2) Then
                If d3(i, 1) <> "" Then
                    ctr = ctr + 1
                    MyDict1(ctr) = d3(i, 1)
                    If datacol2 <> "" Then MyDict2(ctr) = d4(i, 1)
                End If
            End If
        Next i
    Next w
    
    r1 = MyDict1.items
    If datacol2 <> "" Then r2 = MyDict2.items
    
    My3DStats = "Error"
    
    Select Case UCase(func)
        Case "MAX"
            My3DStats = WorksheetFunction.Max(r1)
        Case "MIN"
            My3DStats = WorksheetFunction.Min(r1)
        Case "AVERAGE"
            My3DStats = WorksheetFunction.Average(r1)
        Case "MODE"
            My3DStats = WorksheetFunction.Mode(r1)
        Case "STDEV.S"
            My3DStats = WorksheetFunction.StDev_S(r1)
        Case "SKEW"
            My3DStats = WorksheetFunction.Skew(r1)
        Case "SLOPE"
            My3DStats = WorksheetFunction.Slope(r1, r2)
    End Select
    
End Function

Let's say your sheets are listed in cells N2:N4. Then you'd call the function like this:

=my3dstats("Max",$N$2:$N$4,"B",$A3,"F","shipped","I")
=my3dstats("Min",$N$2:$N$4,"B",$A3,"F","shipped","I")
=my3dstats("Average",$N$2:$N$4,"B",$A3,"F","shipped","I")
=my3dstats("Mode",$N$2:$N$4,"B",$A3,"F","shipped","I")
=my3dstats("STDEV.S",$N$2:$N$4,"B",$A3,"F","shipped","I")
=my3dstats("SKEW",$N$2:$N$4,"B",$A3,"F","shipped","I")
=my3dstats("Slope",$N$2:$N$4,"B",$A3,"F","shipped","I","K")

First parameter is the function you want, next is the list of sheets, next is the first criteria column, next is what that must equal, next is the 2nd criteria column, next is what that must equal, last is the column where the data is. (For Slope, notice there is an extra column). Empty cells in the data column are ignored.

I can't say how fast it runs, should be pretty fast. It depends on how much data you have and how many formulas. Not fancy, in your original formula, I noticed that you are finding the MAX of a sum of columns, I and G. You'll need to create a helper column that is =I2+G2, and use that in the UDF. I considered getting fancier, but regained my senses! Hope this gives you some ideas.