# Statistical functions using 3D array with conditions/filters

#### DaleW

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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Domenic

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

Code:
``=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!

Last edited:

#### DaleW

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

Code:
``=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

##### MrExcel MVP
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

##### New Member
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

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

Code:
``=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

##### MrExcel MVP
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

##### MrExcel MVP
Eric, thanks for clarifying Average of Averages.

Cheers!

#### DaleW

##### New Member
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

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

Code:
``````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.

Replies
9
Views
456
Replies
8
Views
141
Replies
1
Views
191
Replies
1
Views
541
Replies
5
Views
376

1,195,748
Messages
6,011,429
Members
441,614
Latest member
TiaGtz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back