Excel T.TEST regular formula returns different result than array formula

Question

As can be seen, the result differs, although the cells provided as input for calculation are same.

=T.TEST(B1:B4,B5:B8,2,2)
{=T.TEST(IF($A$1:$A$8="a",B$1:B$8,""),IF($A$1:$A$8="b",B$1:B$8,""),2,2)}

Answer 1

This seems like a bug in array formula, because blank cells are treated differently:

• if T.TEST is a non-array formula, blank cells are omitted;
• if T.TEST is inside an array formula, then it does not omit them, but instead treats blank cells as zeros.

If array formula is required, one would need to explicitly check for blank cells, which can be done like this:

{=T.TEST(IF(($A$1:$A$8="a")*(B$1:B$8<>""),B$1:B$8,""),IF(($A$1:$A$8="b")*(B$1:B$8<>""),B$1:B$8,""),2,2)}

In Excel array formulas AND cannot be used, so instead of AND($A$1:$A$8="a",B$1:B$8<>"") we write ($A$1:$A$8="a")*(B$1:B$8<>"").