t test with multiple (2) criteria not returning expected result

Has anyone ever check their multiple criteria ttest against a manually populated ttest and get different results???

Short but useless answer -- Yes, someone somewhere has had this problem.

Half of programming is debugging, and that is just as true for spreadsheets as it is for other programming languages. I'm not sure how much help we can be in debugging this (without the raw data, anyway). There must be something different about your manually extracted array, and the array built by the IF(...),IF(...) part of the function. Debugging this is all about figuring out what those differences are, then figure out why they are different, then figure out what you need to do differently.

My next first step: Pull up the Evaluate formula tool (https://support.office.com/en-us/art...6-a70aa409b8a7 ) and begin to step through the function. This will require patience, and will likely be accompanied by grumblings and groanings and cursings and "why oh why can't MS make a better formula evaluater debugging tool!". It appears that your formula builds an array of ~130 values, so you will need to figure out in the formula evaluater where those 130 values are different from manually extracted array that you are testing against.

Originally Posted by bjmatush

In the matrix formula, missing values are counted as 0 and included in the analysis.

What do you mean by "missing values"?

If you mean rows which do not satisfy your criteria, then you are incorrect, since, within your formula, i.e.:

=T.TEST(IF(($B$6:$B$70=$A$85)*($C$6:$C$70=$B$85),D6:D70),IF(($B$6:$B$70=$D$85)*($C$6:$C$70=$E$85),D6:D70),$B$86,$B$87)

the results of the two clauses:

IF(($B$6:$B$70=$A$85)*($C$6:$C$70=$B$85),D6:D70)

and:

IF(($B$6:$B$70=$D$85)*($C$6:$C$70=$E$85),D6:D70)

for rows which do not satisfy the two criteria in parentheses will result in a Boolean FALSE, not zero. And Boolean FALSEs are ignored by T.TEST's array1 and array2 parameters.

If you had written your formula thus:

=IFERROR(T.TEST(IF(($B$6:$B$70=$A$85)*($C$6:$C$70=$B$85),D6:D70,0),IF(($B$6:$B$70=$D$85)*($C$6:$C$70=$E$85),D6:D70,0),$B$86,$B$87),"")

then you would be correct in your assessment, and this would indeed give a different result from that manually calculated, since, as you say, the zeroes would then be considered legitimate values to be passed to T.TEST.

Regards

In the matrix formula, missing values are counted as 0 and included in the analysis.

Interesting. You have left the "value_if_false" argument out of the IF() formulas, so I would have expected the "missing" "do not include" values to be the boolean FALSE rather than 0 (and the t.test function should ignore boolean values). I wonder why your function is converting the missing values to 0 rather than FALSE? I might suggest that you double check your evaluation and make certain that you are not seeing the 0's that are part of the "test condition" array (FALSE*FALSE=0 in the test condition part of the IF() functions).

Whatever the reason, I might first try filling in some text string for the "value_if_false" arguments and see if that makes a difference. The T.TEST function should ignore text strings just like it ignores blanks.