Calculating Standard Deviation within one record

[carlyle]

I have a field in a Filemaker 4 file defined as standard deviation. This field takes four other fields in the SAME record and calculates the standard deviation of these fields using the following calculation formula: StDev(pH sample 1, pH sample 2, pH sample 3, pH sample 4)

This formula works fine almost all of the time. However, occasionally when samples 1, 2, and 3 are the same and sample 4 is blank, the standard deviation puts a "?" in the field instead of calculating the result as "0". Other times, under the same circumstances (1, 2, and 3 are the same and sample 4 is blank), it correctly calculatees the result as "0"

What is happening??? How can I fix this???

Some examples:

Sample 1 Sample 2 Sample 3 Sample 4 Standard Deviation

7.8 7.8 7.8 blank 0.00

7.4 7.4 7.4 blank ?

Argh! Thanks for your help!!!

[SteveB]

Could you possibly have a space in the empty field which would be interpreted as text and not a number?

Steve

[RalphL]

What is the significance of the standard deviation in a set of only 4 numbers?

[carlyle]

Good Idea, but I checked to make sure there is no space in the fourth field, and it still is giving me a "?" in the standard deviation. (bummer)

The significance of the standard deviation of 4 values is that we are using this as a sample of a population and estimating the population using this (albeit small) sample size. Don't ask me, I'm not the biologist, just the computer nerd. They want the SD, I want to give them a program that can calculate it...

[RalphL]

The ? means that there is an error. If you only enter one value, StDev will give a "?".

I get the same thing for 3 7.4's. Every other number seems OK.

Are you using StDevP or StDev? You should be using StDevP.

I calculated StDevP with my own formulas an it works even for 3 7.4's.

Using A, B, C & D as the data fields:

N = Count(A, B, C, D)

Part1 = (A ^ 2 + B ^ 2 + C ^ 2 + D ^ 2) / N

Part2 = ((A + B + C + D) / N) ^ 2

StDevP = Sqrt(P1 - P2)

I broke it up so I could see if there were any problems. You may want to use the round function to limit the number of places in the result.

[carlyle]

This works! Thank you Ralph.

However, I still can't make it work when I try to write it as one formula:

Sqrt(((A ^ 2 + B ^ 2 + C ^ 2 + D ^ 2)/(N - 1)) -

((A + B + C + D) ^ 2)/(N * (N - 1)))

(As you can probably see, I am using StDev, not StDevP...don't ask me...ask the biologists... I tried arguing this one with them, but this is apparently what you do when you are doing river sampling).

It works when I separate it into 2 fields:

Dumb Field = (A ^ 2 + B ^ 2 + C ^ 2 + D ^ 2)/(N - 1) -

((A + B + C + D) ^ 2)/(N * (N - 1))

The Real StDev = StDev(Dumb Field)

That works just fine (and it's what I'm going to do for now), but it seems like the first formula should work! Is this just a flaw with FileMaker, or am I mixing up my parenthesis, or what???

Thanks SO much for your help...I'm very glad to be rid of the pesky question marks and I can live with the pesky extra field definitions.

Carlyle