conditional stdev

Hi KeSue,

Build your function is two steps.

Add a column (D in the example) into which you will import the "a.m." values, and place a null string into the cells on rows with "p.m." values, using an IF statement.

Use STDEV or STDEVP to determine the standard deviation in this column.

In the example, I've used RANDBETWEEN to generate a set of random numbers in column D. I then, to check the effect of a half empty column on the results, copied half that list and used Paste Formula Results to paste the same set of 10 numbers into the upper (AM) and lower (PM) halves of the column.

As can be seen at the bottom, the STDEVP result is the same for the double set (column C as it is for the single set (and 10 cells containing null strings) in column D. The function does not count the null string values in the 'empty' half of the day.

Formulas:

D2, and filled down to D21: IF(B2=D$23,C2,"")

D23 contains the text to be matched in column B for the value in that row of column C t be copied to the same row of column D.

D22: STDEVP(D)

The same formula, with C in the parentheses, is in C22, but is not needed in your table.

Lookup STDEV in the Function Browser for details on the differences between STDEV, STDEVP (used here), STDEVA and STDEVPA. All four perform the same job, but with different parameters, one of which shuld fit your case.

Regards,

Barry

Hi KeSue,

I took a different approach from Barry's because I was unsure how STDEV would handle blank cells.

Try sorting the data by column B and apply STDEV to a.m. and p.m separately.

Perhaps best to duplicate the table (to keep the original table intact).

Formula in C10 =STDEV(C2:C5)

Formula in C11 =STDEV(C6:C9)

Just an example. STDEV on four values is not statistically valid 😉.

Also I second Barry's advice to Look up STDEV in the Function Browser for details on the differences between STDEV, STDEVP, STDEVA and STDEVPA.

Regards,

Ian.

Hi Barry,

thanks for the detailed feedback. I have already applied the solution you recommend, but creating the IF tables for a.m. and p.m. in a separate sheet; I collect three figures in the morning and three in the evening, using a Numbers form on my ipad or iphone, and adding 6 extra columns to the data sheet quickly makes the form untidy on the ipad and all but useless on the iphone.

What I was looking for is a sequence of functions I could string together in order to perform the IF and STDEV actions in a cell, emulating Excel's DSTDEV; basically, a "STDEVIF" function. I guess that is not possible, but the less elegant solution I've implemented works, so my work is getting done!

Many thanks again, and best regards

Keith

Hi Ian,

thanks for the detailed feedback. As I have said to Barry, I have already applied the solution he recommend, but creating the IF tables for a.m. and p.m. in a separate sheet; I collect three figures in the morning and three in the evening, using a Numbers form on my ipad or iphone, and adding 6 extra columns to the data sheet quickly makes the form untidy on the ipad and all but useless on the iphone.

What I was looking for is a sequence of functions I could string together in order to perform the IF and STDEV actions in a cell, emulating Excel's DSTDEV; basically, a "STDEVIF" function. I guess that is not possible, but the less elegant solution I've implemented works, so my work is getting done!

By the way, in the IF function I used "n/a" as the if-false option, and Numbers worked quite happily to calculate the SD. Interestingly, before I used the "n/a" text I tried a zero, and with half the numbers being zero, that skewed the SD calculation significantly!

Many thanks again, and best regards

Keith

P.S. With over 400 enteries in the sheet (and its growing daily) , I think my SD calculations are probably statistically valid now 😉.

K