Array formula within VBA

[alfordtp]

I'm trying to VBA code a few calculations. As an Excel array (control-shift-enter) formula, it reads like {=TRIMMEAN(IF(B:B="X",C:C),H2)}, where it is looking at a set of groups (B:B = "X"; find rows that contain "X" in column "B") , then calculating the Trimmean function for column "C" in those rows only. (H2 being the % for Trimmean)

I'm not sure how to do this in VBA.

I'm assuming its some form of WorksheetFunction.TrimMean (Range (If...

This is only one of the array calculations I need to do, so I'm hoping if I can learn how to do this, I can use this example to do the rest of my formulas.

Thanks in advance,

Tim

 

[BSALV]

with that intersect, you are not using the whole B&C-columsn, just the used part
The evaluate is just like you write it in a cell, but you have to double the double quote "
Also the ranges are variables here, so there you stop the formula with a double quote and insert the range

Sub T_Mean()
     With ActiveSheet                                           'your active sheet !!!!
          Set c = Intersect(.UsedRange, .Range("B:B"))          'used part of Bf
          myvalues = Evaluate("=if(" & c.Address & "=""-""," & c.Offset(, 1).Address & ",""x"")")     'an array with the C-value of B-cell is "x"
          MyTrimMean = Application.WorksheetFunction.TrimMean(myvalues, .Range("H2").Value)
     End With
End Sub

 

[alfordtp]

@BSALV

thanks for your response. however, I'm not sure if I'm completely following. I've attached an example file of what I'm trying to do.

Let me fully explain my end goal: I'm trying to analyze margins, but exclude the outliers. In order to do that, first I'm calculating the Trimmean. If I understand how Trimmean works, if there are 100 records and you filter out 20%, it will take the top 10 and bottom 10 records out of the calculation. I also want to find the standard deviation of that subset, but (as far as I know) Excel wont do that with a standard formula. So, I'm trying to create a UDF that will calculate the Trimmean and the StdDev of that subset, then identify the records that fall lower than Trimmean - (2 * StdDev).

My attachment shows how I would do it manually, but it takes up quite a bit of resources, so I was hoping a UDF might do it more efficiently.

My end state would look like a set of data on the far left, with part number, group, and margin columns, then on the right (a few columns over), a column of the unique group ids and the Trimmean percentage identified. I would run my UDF in the cells to the right of the margin column.

Excel Example

 

[BSALV]

excel example
you're wrong with the "-" and "x" in your upload #3.
An adjusted macro and 2 UDF's, the one with a dictionary can be used in another sheet then where your data is.
(the other one with evaluate can also be adapted to do the same, but i'm lazy).

In K9:N11 some calculations.
In column E the result of the macro in green and with conditional formatting in blue the one's within the 10%-90% percentile

UDF Example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
7												x	y	z
8	Part	Family	Margin								with UDF_Evaluate	0,542727273	0,588889	0,54833333
9	X1	X	10%	!	0,1			percentiel 10%	0,168		with UDF_dictionary	0,542727273	0,588889	0,54833333
10	X2	X	11%	!	0,11			percentiel 90%	0,844		with macro	0,542727273
11	X3	X	50%		0,5						with array formula	0,542727273	0,588889	0,54833333
12	X4	X	90%		0,9
13	X5	X	60%		0,6
14	X6	X	55%		0,55
15	X7	X	48%		0,48
16	X8	X	70%		0,7
17	X9	X	65%		0,65
18	X10	X	60%		0,6
19	X11	X	88%		0,88
20	X12	X	50%		0,5
21	X13	X	40%		0,4
22	Y1	Y	60%		-
23	Y2	Y	25%		-
24	Y3	Y	50%		-
25	Y4	Y	20%	!	-
26	Y5	Y	60%		-
Sheet1

Cell Formulas
Range	Formula
L8:N8	L8	=udf_trimmean1($B$9:$B$38,L$7,$C$9:$C$38,$H$2)
L9:N9	L9	=udf_trimmean2($B$9:$B$38,L$7,$C$9:$C$38,$H$2)
I9	I9	=PERCENTILE($E$9:$E$38,0.1)
I10	I10	=PERCENTILE($E$9:$E$38,0.9)
L11:N11	L11	=TRIMMEAN(IF($B:$B=L$7,$C:$C),$H$2)
B9:B26	B9	=LEFT(A9,1)
D9:D26	D9	=IF(C9<VLOOKUP(B9,$F:$N,9,FALSE),"!","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E9:E38	Expression	=MEDIAAN($I$9:$I$10;$E9)=E9	text	NO

Sub tester()

     With ActiveSheet                                           'your active sheet !!!!
          Set c = .Range("B9:B38")                              'the range with the x's
          MsgBox "=if(" & c.Address & "=""x""," & c.Offset(, 1).Address & ",""-"")"     '--->just a msgbox for demo
          myvalues = Evaluate("=if(" & c.Address & "=""x""," & c.Offset(, 1).Address & ",""-"")")     'array that contains the result of this evaluate
          .Range("E9").Resize(UBound(myvalues)).Value = myvalues     'write that array for demo to E9:E38

          MyTrimMean = Application.WorksheetFunction.TrimMean(myvalues, .Range("H2").Value)     'the normal TrimMean-formula
          .Range("L10").Value = MyTrimMean
     End With
End Sub

Function UDF_TrimMean1(Crit_Range As Range, Crit_Value, Val_range As Range, Trim_Perc)
     '**********************************************************************
     'CAUTION : can not be used in another sheet (otherwise i need to add the parent sheetname)
     '**********************************************************************
     'MsgBox "=if(" & Crit_Range.Address & "=" & Crit_Value & "," & Val_range.Address & ",""-"")"     '----> unnecessary, but only to show you the formula within that "evaluate"
     myvalues = Evaluate("=if(" & Crit_Range.Address & "=""" & Crit_Value & """," & Val_range.Address & ",""-"")")
     UDF_TrimMean1 = Application.WorksheetFunction.TrimMean(myvalues, Trim_Perc)
End Function

Function UDF_TrimMean2(Crit_Range As Range, Crit_Value, Val_range As Range, Trim_Perc)
     Set dict = CreateObject("scripting.dictionary")
     c = Crit_Range.Value                                       'read your family to an array
     v = Val_range.Value                                        'read your values to an array
     For i = 1 To UBound(c)                                     'loop through your data
          If StrComp(c(i, 1), Crit_Value, vbTextCompare) = 0 Then dict.Add dict.Count, v(i, 1)     'add the matching values to the dictionary
     Next
     If dict.Count = 0 Then UDF_TrimMean2 = "my error": Exit Function
     UDF_TrimMean2 = Application.WorksheetFunction.TrimMean(dict.items, Trim_Perc)
End Function

 

[alfordtp]

Wow! Thank you so much for putting some time into trying to help me with this problem. I need a little bit to digest. Will let you know if I have any more issues.

 

[alfordtp]

this is working. I appreciate all the help with this. I do have a follow-up question:

Like I mentioned before, I'm trying to combine this to also calculate whether values are lower than 2 x StdDev, but the StdDev needs to be of the same range used for the trimmean calculation.

I tried calculating the upper and lower bound limits, then creating an array function combining multiple conditions, trying to mimic my Excel equation:

{=STDEV(IF((B:B=D2)*(C:C>M2)*(C:C<L2),C:C))}

upbound = Application.WorksheetFunction.Large(myvalues, rectrim)
lowbound = Application.WorksheetFunction.Small(myvalues, rectrim)
   

  
   
stdrange = Evaluate("=if((" & Crit_Range.Address & "=" & Crit_Value & "," & Val_range.Address & ")*(" & myvalues & ">" & lowbound & ")*(" & myvalues & "<" & upbound & ")),-""")
    
    
    
    
UDF_stdev1 = Application.WorksheetFunction.StDev(stdrange)

Can you see if I'm making any obvious mistakes in this? It is not giving me an answer to what the StdDev is of the smaller set of records where all conditions are met.

 

[BSALV]

i can't follow ..., rectrim, what's that ?
you eliminate 10% for example, =5% lower end + 5% upper end and then you check for 2*Stdev for the remaining 90% ?