Stand Deviation Expression

A

Azkid7k

I put this in the expression builder and I don't get an answer. Can you help
me?

=[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))
 
J

Jeff Boyce

"How" depends on "what" ... in this instance, if I'm interpreting your
statement, your "what" (your data structure) is more like a spreadsheet than
a relational database.

The function Access offers to calculate standard deviation works on a column
of data, not "across columns" (like a spreadsheet).

Please post back a description of your data structure.

Please post back a more complete description of what happens -- "don't get
an answer" could mean a lot of different things...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

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!!


Jeff Boyce said:
"How" depends on "what" ... in this instance, if I'm interpreting your
statement, your "what" (your data structure) is more like a spreadsheet than
a relational database.

The function Access offers to calculate standard deviation works on a column
of data, not "across columns" (like a spreadsheet).

Please post back a description of your data structure.

Please post back a more complete description of what happens -- "don't get
an answer" could mean a lot of different things...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Azkid7k said:
I put this in the expression builder and I don't get an answer. Can you
help
me?

=[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))
Click to expand...
Click to expand...
 
J

Jeff Boyce

Please re-read my response.

Your description sounds like you could have put these "scores" in a
spreadsheet, using one column per panelist.

But if this is how you've entered the scores in Access, you will NOT be able
to use Access' standard deviation function, because it "expects" the data to
be organized in a single column.

If you need standard deviation across columns, consider using Excel.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Azkid7k said:
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!!


Jeff Boyce said:
"How" depends on "what" ... in this instance, if I'm interpreting your
statement, your "what" (your data structure) is more like a spreadsheet
than
a relational database.

The function Access offers to calculate standard deviation works on a
column
of data, not "across columns" (like a spreadsheet).

Please post back a description of your data structure.

Please post back a more complete description of what happens -- "don't
get
an answer" could mean a lot of different things...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Azkid7k said:
I put this in the expression builder and I don't get an answer. Can you
help
me?

=[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))
Click to expand...
Click to expand...
Click to expand...
 
A

Azkid7k

If you have over 1000 products being tested against 1000 products and you
have anywhere from 9 to 12 panelist giving scores on both products. Plus you
have to toal the difference in each score. How else could you build this so
that each product and compairison product is connected to each panelist and
their scores.

Jeff Boyce said:
Please re-read my response.

Your description sounds like you could have put these "scores" in a
spreadsheet, using one column per panelist.

But if this is how you've entered the scores in Access, you will NOT be able
to use Access' standard deviation function, because it "expects" the data to
be organized in a single column.

If you need standard deviation across columns, consider using Excel.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Azkid7k said:
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!!


Jeff Boyce said:
"How" depends on "what" ... in this instance, if I'm interpreting your
statement, your "what" (your data structure) is more like a spreadsheet
than
a relational database.

The function Access offers to calculate standard deviation works on a
column
of data, not "across columns" (like a spreadsheet).

Please post back a description of your data structure.

Please post back a more complete description of what happens -- "don't
get
an answer" could mean a lot of different things...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I put this in the expression builder and I don't get an answer. Can you
help
me?

=[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))
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

If you have over 1000 products being tested against 1000 products and you
have anywhere from 9 to 12 panelist giving scores on both products. Plus you
have to toal the difference in each score. How else could you build this so
that each product and compairison product is connected to each panelist and
their scores.
Click to expand...

By using a properly normalized structure, with three tables:

Products
ProductID
<information about the product>

Panelists
PanelistID
<name, other info about the panelist>

Scores
PanelistID <who did the testing>
ProductID <what did they test>
Score <how did this product score>

If you wanted to see all million comparisons (and had some patience) you can
then use a Query such as:

SELECT Score1.ProductID AS Product1, Score2.ProductID AS Product2,
Avg(Score1.Score - Score2.Score) AS AvgDiff,
StDev(Score1.Score-Score2.Score) AS StdDevDiff
FROM Scores AS Score1, Scores AS Score2
GROUP BY Score1.ProductID, Score2.ProductID;

You can also use criteria to select which individual products or subsets of
products to compare.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Standard Deviation 3
Creating a Function Problem 4
Select & Max Function Q 3
How do I match values in a sub set after a sort? 1
Scoring a result in a cell 4
Trying to create an audit database inc calculations 4
Grouping - Distinct 5
Calculate % from list of scores or ratings 3

Top