Previous Topic Next Topic

If and stdev

Closed
Geoff - Oct 14, 2009 at 10:18 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 15, 2009 at 09:31 PM
Hello,

I'm having problems with my 'if' formula with regards to stdev. I created need excel to calculate the standard deviation of certain rows in a column. The rows I would like to have included in the standard deviation calculation are indicated in column C with a "1", and the values in column J. All other rows (that I do not want in the calculation) in column C are blank. All data is in a separate worksheet named Transcription Data. Here is the formula I wrote: =IF('Transcription Data'!c3:c300, ">0", STDEV('Transcription Data'!J3:J300)) However, the formula keeps calculating the standard deviation of the entire J column. What am I doing wrong? I've also tried =STDEV(IF(c3:c300, ">0", J3:J300))

Any suggestions?

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 15, 2009 at 09:31 PM
The sample data is as given below from A1 to B9
1 1
2 2
3 3
2
1 4
1 5
2
3 6
4 7

in C1 copy this formla
=IF(A1=1,B1,0)
co[u C1 down
now in any empty cell copy this fomula
=STDEV(IF(C1:C9<>0,C1:C9))
INVOKE THIS FORMULA WITH CONTROL+SHIFT+ENTER

can you modify this experiment with your data?

if so post confirmation