computing formula according to criteria

[liory]

Can anyone help?

I am trying to compute a formula that will enable me to analyse dat
derived from a neuropsychological experiment where the trials ar
randomised.

I have numerical data in column A, for which I need to calculate th
STANDARD DEVIATION.
The numbers in column A are divided into 3 different conditions, eac
of which is specified in columns B,C and D. These are arranged in
random order.

I need to get the _standard_deviation__ of all values in column A
*only when * column B reads condition"X", column C reads condition "Y
and column D reads condition "Z".

In order to compute the means of those values I have used the formula:

=SUMPRODUCT(A1:A200,((B1:B200="X")*(C1:C200="Y")*(D1200="Z")))/SUM((A1:A200,(B1:B200="X")*(C1:C200="Y")*(D1200="Z"))


This works very well for means, but I don't know how to calculat
STDEV.

It would make my life SO much easier if I knew how to do this. Doe
anyone have a suggestion?
Many many thanks,
Lior

 

[duane]

how about using auto filter and copying only the data you want to a ne
locationand do your analysis there

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=STDEV(IF((B1:B100="X")*(C1:C100="Y")*(D1100="Z"),A1:A100))

you could use the same array formula type for calculating the AVERAGE
as alternative for your SUMPRODUCT formula:
=AVERAGE(IF((B1:B100="X")*(C1:C100="Y")*(D1100="Z"),A1:A100))