Returns the average of values of all dimensions or specified dimensions of a range that meet a chosen set of criteria.

Syntax

=MM_AVERAGEIFS(Average_value, Range1, Condition1, [Range2, Condition2], [Range3, Condition3], ["Name", Index], ["Name2", "Index2"], ...)

• Average_value
  Cells to average if the given set of criteria is met.

• Range1
  Range to be tested for Condition1. 

• Condition1
  The first criterion used to select which values to average. Can be string, number or cell reference. Criterion that includes logical symbols (=, >, <) must be written as a string. Ex: ">0".
  Warning: If your version of Excel does not use English notation, be sure to use a decimal point for string criteria. For instance, write ">12.5" instead of ">12,5" as the cultural translation is not automatic for strings. 

• Range2, Condition2 (optional)
  Range to be tested for Condition2 and second criterion.

• Range3, Condition3 (optional)
  Range to be tested for Condition3 and third criterion.

• "Name", Index (optional)
  String, Integer.
  Name of a loop and index of that same loop you want to access to average values. If you do not specify any index for a loop, Mind takes all indexes for that loop.
  For stochastic dimension: if you want to average the results of one specific simulation, you can specify "SIM" as Name and the desired simulation index under Index.
  Warning: If you specify loops, all first seven parameters have to be input. Input empty strings ("") when needed as Mind does not accept empty parameters.

 Warning: The parameter LoopName is case sensitive.

• "Name2", Index2, "Name3", Index3, ...etc. (optional)
  String, Integer.
  Names and indexes of other loops. 

Remarks

• There is no point in using MM_AVERAGEIFS, if Average_value has no loop or stochastic dimensions; the function automatically returns Average_value without applying the criteria. In that case, you must use AVERAGEIFS Excel function.

• You cannot specify the same loop with different indexes. If you want to sum two different dimensions of the same loop, one possibility is to use twice the MM_RESULT function for each dimension and a AVERAGEIFS Excel function. 

• Remember to input Range2, Condition2, Range3 and Condition3 if you input Name, Index, Name2, Index 2, ...; use empty strings ("") if you have less than 3 criteria.

• For MM_AVERAGEIFS to work correctly, Average_value and Range1, Range2, Range3 must have same range size and loop dimensions. If you use a range and not a single cell in your parameters, the cells of your range also need to have the same dimensions.

• If the set of criteria is never met for the ranges, the function returns the error value: NaN.

• See MM_AVERAGEIF function‍ if you specify only one criterion.

Example 1: No loop and simulations

Formula1: =MM_AVERAGEIFS(A2,B2,">0.5"). Given n the number of simulations in Mind, the formula returns the average of the simulations in A2 for which cell B2 is greater than 0.5. If n=3 and the results in A2 and B2 are (0.8, 0.7, 0.6) and (0.4, 0.51, 0.78) respectively, then A3 returns 0.65.

Example 2: Two loops, no specified dimension

In this example, the cells D3 (name: Sum) and E3 (name: Product) depend on Loop1 and Loop2; they have 2 dimensions each of size 2, which amount to 4 values. These cells correspond to the sum and the product of Loop1 and Loop2, respectively.

The formula :=MM_AVERAGEIFS(Sum,Sum,">10",Product,">500") returns 47.5. Indeed, it is averaging values of Sum for which Sum is greater than 10 and Product is greater than 500. In this case, it is averaging 47 and 48, which correspond to the dimension indexes (Loop1,Loop2) = (2,1) and (Loop1,Loop2) = (2,2). Dimension results are shown below.

Example 3: Two loops, specified dimension

This example is similar to the one above but we choose to specify the index of the second loop. We set it to 1. Note that, in this case, all Range and Condition parameters must be input.

The formula: =MM_AVERAGEIFS(Sum,Sum,">10",Product,">500","","","Loop2",1) returns 47. Indeed, in this case, we only take into account the first index of Loop2. Details are shown below.

Example 4: Loops and simulations

You can mix the first and second examples to average cells with loop and stochastic dimensions.

Example 5: Error example, empty parameters

Formula: =MM_AVERAGEIFS(Sum,Sum,">10",Product,">500",,,"Loop1",1)
Returns an error as Mind does not recognize empty parameters.

Example 6: Error example, ranges with different dimension sizes

Let us go back to example 2. For cell E3, instead of calculating the product, we choose E3=B5. Thus, cell D3 and E3 do not have the same dimension sizes. Indeed, D3 has two loops and E3 only has one. In that case, the formula does not make sense and Mind returns 0.

It is possible to force a value for cell E3 for all indexes of Loop1 so that the formula is calculated properly. See formula 4 in the MM_RESULT function‍ article.