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

 

[Aladin Akyurek]

Can anyone help?

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

I have numerical data in column A, for which I need to calculate the
STANDARD DEVIATION.
The numbers in column A are divided into 3 different conditions, each
of which is specified in columns B,C and D. These are arranged in a
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 calculate
STDEV.

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

 

[Aladin Akyurek]

For the mean, try:

=AVERAGE(IF((B1:B200="X")*(C1:C200="Y")*(D1200="Z")*ISNUMBER(A1:A200),A1:A200))

For the standard deviation, try:

=STDEV(IF((B1:B200="X")*(C1:C200="Y")*(D1200="Z")*ISNUMBER(A1:A200),A1:A200))

If you insert a row before the data with appropriate labels, you can also
invoke Pivot Tables