problem using CHITEST(IF...

Hello,

I'm trying to compute CHITEST for several subsets inside the same sheet. But there is some problem when I combine CHITEST and IF:

Writing:
{=IF($A$2:$A$1606=$A2,$U$2:$U$1606)}
I get: {2,1,1,0,1}
(values in U2:U6)

But if I write:
{=CHITEST((IF($A$2:$A$1606=$A2,$U$2:$U$1606)),{1,1,1,1,1})}
I get: #N/A

However, writing:
=CHITEST({2,1,1,0,1},{1,1,1,1,1})
or
=CHITEST(U2:U6,{1,1,1,1,1})
I get: 0.73


Any idea?

(I'm using Office 2008 for Mac)

Thank you in advance.

Ok, thanks. That's the problem!

Any idea to get rid of all the FALSE, FALSE,... of the first array? i.e. to pass from one array of 1606 values where only 5 are different than FALSE to an array of only those 5 elements.

Thanks a lot shg!

The problem is that, as far as I know, with the office 2008 version for mac VBA is not available. I wish it was.

I've just solved my problem in two steps: (taking into account the chi square formula and that my expected value is always 1):
1. X2 {=SUM(IF($A$2:$A$1606=$A2,$U$2:$U$1606-1,0)^2)}
computes chi square test

2. =CHIDIST(X2,I2-1)
where I2 has the number of observations.


Anyway, whether I can ever use VBA I will use you function which is more general