I have an excel formula right as follows:

=AVERAGE(AVERAGEIFS(Perc;FL;G$2;CER;{"n/a";"Not Required";"Unknown"}))

How can I calculate the counter of this, or in other words average of Perc when CER is not equal to "n/a", "Not Required" or "Unknown"

CodePudding user response：

First the Formula you have will not return the true average of the found criteria but the average of the average.

For example lets say your data has 2-2,10-3,100-4 the true average of the 112 numbers would be close to 4, but if you took the average of the 2s, the average of the 3s then the average of the 4s, then averaged those you would get 3. Which is how the formula you showed is doing it.

So instead use:

=SUMPRODUCT(SUMIFS(Perc;FL;G$2;CER;{"n/a";"Not Required";"Unknown"}))/SU MPRODUCT(COUNTIFS(FL;G$2;CER;{"n/a";"Not Required";"Unknown"}))

Now for the answer to your question. We use ISERROR(MATCH()) to find where it does not equal those:

=SUMPRODUCT(Perc*(ISERROR(MATCH(CER,{"n/a";"Not Required";"Unknown"},0)))*(FL=G$2))/SUMPRODUCT((ISERROR(MATCH(CER,{"n/a";"Not Required";"Unknown"},0)))*(FL=G$2))

If the value is not found in the array it returns True and the math counts it.