Standard Deviation

[Azkid7k]

I put this expression in the espression builder and no calculations come out.
I am at a lost. Please help.


=[StDev](IIf(Not IsNull([1 GV DOD Score]),[1 GV DOD Score],0)+IIf(Not
IsNull([2 GV DOD Score]),[2 GV DOD Score],0)+IIf(Not IsNull([3 GV DOD
Score]),[3 GV DOD Score],0)+IIf(Not IsNull([4 GV DOD Score]),[4 GV DOD
Score],0)+IIf(Not IsNull([5 GV DOD Score]),[5 GV DOD Score],0)+IIf(Not
IsNull([6 GV DOD Score]),[6 GV DOD Score],0)+IIf(Not IsNull([7 GV DOD
Score]),[7 GV DOD Score],0)+IIf(Not IsNull([8 GV DOD Score]),[8 GV DOD
Score],0)+IIf(Not IsNull([9 GV DOD Score]),[9 GV DOD Score],0)+IIf(Not
IsNull([10 GV DOD Score]),[10 GV DOD Score],0)+IIf(Not IsNull([11 GV DOD
Score]),[11 GV DOD Score],0)+IIf(Not IsNull([12 GV DOD Score]),[12 GV DOD
Score],0))

 

[BruceM]

I don't see what is going on with StDev at the beginning, but I don't think
it will give you an expression that can be evaluated.

You could use Nz instead of the IIf statements:

Nz([1 GV DOD Score],0) + Nz([2 GV DOD Score],0)

I still don't see what is supposed to happen with StDev. Maybe you are
trying to multiply the result?
=[StDev] * (Nz([1 GV DOD Score],0) + Nz([2 GV DOD Score],0)...)

I have to wonder about the design with the fields numbered 1 through 12.
For one thing, if you ever add or remove fields you will need to rewirte
your expressions, queries, and so forth. Storing the information in a
related table is usually the best approach, although I don't know anything
about what you are trying to do with this database.

 

[Azkid7k]

Thank youf for Helping me. Its a taste calculations.
I have a product UPC code and I have a Comparision product.
I have anywhere from 9 to 12 panelist tasting both products and than giving
them a score.

So, 1 GV SCORE is for one panelist.
2 GV SCORE is for second panelist
so on and so forth.
I need to get the Standard Deviation of the total panelist. for each product.
I thought I could do that with the below expression. but its not working.

the length of the equation is as follows, but I don't know how to put that
in the expression builder.

To get the standard deviation, just take the square root of the resulting
numberI know this sounds confusing, but just check out this example:
your list of numbers: 1, 3, 4, 6, 9, 19
mean: (1+3+4+6+9+19) / 6 = 42 / 6 = 7
list of deviations: -6, -4, -3, -1, 2, 12
squares of deviations: 36, 16, 9, 1, 4, 144
sum of deviations: 36+16+9+1+4+144 = 210
divided by one less than the number of items in the list: 210 / 5 = 42
square root of this number: square root (42) = about 6.48

Thank you again for your help!!

I don't see what is going on with StDev at the beginning, but I don't think
it will give you an expression that can be evaluated.

You could use Nz instead of the IIf statements:

Nz([1 GV DOD Score],0) + Nz([2 GV DOD Score],0)

I still don't see what is supposed to happen with StDev. Maybe you are
trying to multiply the result?
=[StDev] * (Nz([1 GV DOD Score],0) + Nz([2 GV DOD Score],0)...)

I have to wonder about the design with the fields numbered 1 through 12.
For one thing, if you ever add or remove fields you will need to rewirte
your expressions, queries, and so forth. Storing the information in a
related table is usually the best approach, although I don't know anything
about what you are trying to do with this database.

I put this expression in the espression builder and no calculations come
out.
I am at a lost. Please help.


=[StDev](IIf(Not IsNull([1 GV DOD Score]),[1 GV DOD Score],0)+IIf(Not
IsNull([2 GV DOD Score]),[2 GV DOD Score],0)+IIf(Not IsNull([3 GV DOD
Score]),[3 GV DOD Score],0)+IIf(Not IsNull([4 GV DOD Score]),[4 GV DOD
Score],0)+IIf(Not IsNull([5 GV DOD Score]),[5 GV DOD Score],0)+IIf(Not
IsNull([6 GV DOD Score]),[6 GV DOD Score],0)+IIf(Not IsNull([7 GV DOD
Score]),[7 GV DOD Score],0)+IIf(Not IsNull([8 GV DOD Score]),[8 GV DOD
Score],0)+IIf(Not IsNull([9 GV DOD Score]),[9 GV DOD Score],0)+IIf(Not
IsNull([10 GV DOD Score]),[10 GV DOD Score],0)+IIf(Not IsNull([11 GV DOD
Score]),[11 GV DOD Score],0)+IIf(Not IsNull([12 GV DOD Score]),[12 GV DOD
Score],0))

 

[BruceM]

I took your question rather literally, and suggested another way to approach
it. With this new information I can tell you that your best bet almost
surely is to have the scores in a related table. A query could be used to
sum the numbers, count the number of panelists, and perform the necessary
calculations. If you are using a flat (one-table) database you will need to
find a way to adjust the number of panelists.

In general I think you will need something like this:

tblComparison
ComparisonID
ComparisonProduct
etc.

tblPanelistScore
PanelistScoreID
ComparisonID
PanelistID
Score

If the panelists are identified as specific individuals you will also need a
Panelists table:

tblPanelist
PanelistID
FirstName
LastName

In that case tblPanelistScore is a junction table between tblComparison and
tblPanelist. However, if the panelists are just anonymous numbers there is
no need for tblPanelist. PanelistID in tblPanelistScore could just be the
number (Panelist 1, Panelist 2, etc.)

For each Comparison you would have a listing of scores. Calculations could
be performed in a query based on tblPanelistScore. However, I need to say
that the scope of the question is beyond what I thought when I first
replied. Others are much better qualified to advise you how to proceed.

Thank youf for Helping me. Its a taste calculations.
I have a product UPC code and I have a Comparision product.
I have anywhere from 9 to 12 panelist tasting both products and than
giving
them a score.

So, 1 GV SCORE is for one panelist.
2 GV SCORE is for second panelist
so on and so forth.
I need to get the Standard Deviation of the total panelist. for each
product.
I thought I could do that with the below expression. but its not working.

the length of the equation is as follows, but I don't know how to put that
in the expression builder.

To get the standard deviation, just take the square root of the resulting
numberI know this sounds confusing, but just check out this example:
your list of numbers: 1, 3, 4, 6, 9, 19
mean: (1+3+4+6+9+19) / 6 = 42 / 6 = 7
list of deviations: -6, -4, -3, -1, 2, 12
squares of deviations: 36, 16, 9, 1, 4, 144
sum of deviations: 36+16+9+1+4+144 = 210
divided by one less than the number of items in the list: 210 / 5 = 42
square root of this number: square root (42) = about 6.48

Thank you again for your help!!

I don't see what is going on with StDev at the beginning, but I don't
think
it will give you an expression that can be evaluated.

You could use Nz instead of the IIf statements:

Nz([1 GV DOD Score],0) + Nz([2 GV DOD Score],0)

I still don't see what is supposed to happen with StDev. Maybe you are
trying to multiply the result?
=[StDev] * (Nz([1 GV DOD Score],0) + Nz([2 GV DOD Score],0)...)

I have to wonder about the design with the fields numbered 1 through 12.
For one thing, if you ever add or remove fields you will need to rewirte
your expressions, queries, and so forth. Storing the information in a
related table is usually the best approach, although I don't know
anything
about what you are trying to do with this database.

I put this expression in the espression builder and no calculations come
out.
I am at a lost. Please help.


=[StDev](IIf(Not IsNull([1 GV DOD Score]),[1 GV DOD Score],0)+IIf(Not
IsNull([2 GV DOD Score]),[2 GV DOD Score],0)+IIf(Not IsNull([3 GV DOD
Score]),[3 GV DOD Score],0)+IIf(Not IsNull([4 GV DOD Score]),[4 GV DOD
Score],0)+IIf(Not IsNull([5 GV DOD Score]),[5 GV DOD Score],0)+IIf(Not
IsNull([6 GV DOD Score]),[6 GV DOD Score],0)+IIf(Not IsNull([7 GV DOD
Score]),[7 GV DOD Score],0)+IIf(Not IsNull([8 GV DOD Score]),[8 GV DOD
Score],0)+IIf(Not IsNull([9 GV DOD Score]),[9 GV DOD Score],0)+IIf(Not
IsNull([10 GV DOD Score]),[10 GV DOD Score],0)+IIf(Not IsNull([11 GV
DOD
Score]),[11 GV DOD Score],0)+IIf(Not IsNull([12 GV DOD Score]),[12 GV
DOD
Score],0))