Obtain conditionally filtered array of values in Excel

Question

I am looking to do an F.Test, T.Test, Var.S with a data subset based on conditionally meeting criteria of other cells. In order to do this, I need an array that represents that data subset that conditionally meets my criteria. When calculating the average, I was able to do AVERAGEIFS(), but that same conditional structure is not available for F.Test, T.Test, and Var.S. Sample dataset below.

    A            B           C             D
1  Month      Employee      Part         Value
2  Jan          Tom          A            50    
3  Jan          Bob          A            55
4  Jan          Sally        B            40
5  Feb          Bob          A            45
6  Feb          Tom          A            75
7  Feb          Bob          A            60
8  Feb          Bob          B            65
9  Feb          Bob          A            55
10 Mar          Sally        B            70
11 Mar          Tom          B            40
12 Apr          Bob          B            50
13 Apr          Sally        A            45   
14 Apr          Sally        A            60
15 May          Bob          A            55
16 May          Tom          B            55
17 May          Bob          A            50
18 May          Sally        B            60

For example, if I wanted to know the Variance of the "Value" column from Bob in February with Part A, This array would consist of 3 values: 45,60,55. I want to be able to do Var.S(CODE I NEED GOES HERE)

In my real dataset, I have thousands of rows of data, so while for my example manually entering VAR.S(D5, D7, D9) would accomplish what I want, that is not feasible for the real data set.

I think whatever solution gets used to obtain a conditionally filtered array to use for VAR.S could be applied for the arrays needed for T.TEST, and F.TEST as well, but if that is not the case, then I would appreciate a solution to that as well.

Any help that can be provided is much appreciated!

Answer 1

There is a standard method to creating a basic array formula that introduces conditions to functions that do not normally take conditions.

'basic formula
=FUNCTION(<range1>, [optional range2], [optional range3], ...)
'array formula with one condition
=FUNCTION(IF(<condition1>, <range1>))
'array formula with two conditions
=FUNCTION(IF(<condition1>, IF(<condition2>, <range1>)))

After typing or pasting these array formulas into the worksheet's formula bar, they need to be finished with ctrl+shift+enter (aka CSE); not just enter. This will put maths braces around the formula like this,

{=FUNCTION(IF(<condition1>, <range1>))}

You don't type these in yourself. Excel will add them if you put the array formula in properly with CSE.

It is important to note that the two conditions were accomplished by nesting two IF statements. Do not try to put two or more conditions together with AND or OR. AND and OR are already a form of cyclic calculation and will not operate properly within an array formula.

It is also important to note that array formulas calculate everything you supply to them. Do not use full column references; try to cut ranges down to the size used with a minimum of unused cells. If you have to reference frequently changing ranges, there are methods of correctly referencing them dynamically.

Formulas for your same data:

'in G5:H5 per supplied image (Bob)
=VAR.S(D3, D5, D7:D9, D12, D15, D17)       'standard formula
=VAR.S(IF(B2:B18="bob", D2:D18))      'array formula with CSE

'in G8:H8 per supplied image (Bob, Feb)
=VAR.S(D5, D7:D9)                                    'standard formula
=VAR.S(IF(B2:B18="bob", IF(A2:A18="Feb", D2:D18)))  'array formula with CSE

'in G11:H11 per supplied image (Bob, Feb, A)
=VAR.S(D5, D7, D9)                                                  'standard formula
=VAR.S(IF(B2:B18="bob", IF(A2:A18="Feb", IF(C2:C18="A", D2:D18))))  'array formula with CSE

You can see from the above paired examples that manually selecting a diverse set of cells and conditionally filtering with an array formula produce the same results.