Microsoft Excel - How to use ttest and match certain condition

Asked By Ewa P on 08-Dec-12 11:25 AM
I want to test 2 columns when they match certain condition.
data in on 2 sheets, the conditions values are the same on both sheets

(columns reference are determined by the drop down list ---->cell y2 and z2 : refer to 2 sheets
column reference is in SelLIST A2:C11
the formual below works when I match only 1 condition (value in X4)

I've got another condition in cell y3 I want to add to the formula, any idea how?
Thank you 

=TTEST(IF(INDIRECT(VLOOKUP($X4,SelLIST!$A$2:$C$11,3,FALSE))=$X4,INDIRECT($Y$2)),
IF(INDIRECT(VLOOKUP($X4,SelLIST!$A$2:$C$11,3,FALSE))=$X4,INDIRECT($Z$2)),2,1)
Harry Boughen replied to Ewa P on 07-Dec-12 11:45 PM
Hello Ewa,

I am not sure what your IF statements are doing, they are identical and there is no ELSE option given so either the formula does nothing or it does the ttest on the ranges specified by Y2 and Z2.

Regards

Harry
Ewa P replied to Harry Boughen on 13-Dec-12 05:34 AM
tt.zip
Hi Harry,
I attached example of what I'm trying to do. I managed to get the formula working to be dependent on column and row values, but when I want to include ttest I get an error. 
Thank you for looking at it
Ewa
Harry Boughen replied to Ewa P on 13-Dec-12 06:17 AM
Hello Ewa,
I think the problem is you are trying to do a t-test on only two values.  The first part of your formula evaluates to 14, the second part to 7.  You need to be selecting ranges for the TTEST function and SUMPRODUCT doesn't do that even if you put it in an array formula. 
I suspect that you are likely to need macros to extract the data arrays that you want to test.
I'll think about it some more and if I come up with anything, I'll get back to you.
Regards
Harry
Harry Boughen replied to Ewa P on 13-Dec-12 06:25 AM
Hello again Ewa,
Try deleting the SUMPRODUCT (2 places) and enter the formula as an array.  It gives an answer but only you can judge whether it is the one you want.
Harry
Ewa P replied to Harry Boughen on 18-Dec-12 04:46 AM
ttest in excel.zip

Hi,
I think I've got it sorted now, had to do a bit work around but it seem to give the correct result. I attached example if someone needed it in future : sheet TTcell C132
Thanks you looking at it
Ewa
Harry Boughen replied to Ewa P on 18-Dec-12 05:19 PM
Hello Ewa,
Good one.
I notice that you have it entered as an array formula but I accidentally entered it as a normal formula and it still seems to work OK.
I know it makes it harder to debug, but it would be possible to name parts of your formula (say the TDIST part named as comparison) and it would become more 'readable'  eg IFERROR(IF(comparison<0.0001,"<01%",comparison),"",etc...).  If you have very large spreadsheets with lots of repeated long formulae it results in a smaller file and faster execution.
The other thing is, in IF statements it is a bit more efficient to have the most common outcome of the comparison as the 'then' part rather than the 'else'.  In your case, unlikely to matter too much but could be significant in large projects with lots of comparisons.
Keep up the good work.
Harry