T
Tom
Hi,
I need some help w/ adjusting the example query below to accomodate
"statistical outliers".
Expression: Round(Nz(Sum([TableName].[Score]),0)/[RecordCount],1)
Currently, I utilize the query to show a cumulutive average on a form.
Actually, I do not even show the averaging number (font color is same color
as background color) ... instead, for example, I simply show a "green
textbox" ("green" is based on scores between 90 to 100%)...
....and, this is where the problem lies...
Let's say I have 10 records (e.g. tracking "Student Exams") of which 9 exams
have a score of exactly 100% each.
However, the 10th score equals only 10% (whatever the cause might have
been).
As a result, the cumulutive average equals 91% (900 + 10/10=91). Based on
the overall average, the textbox is "green". Mathematically, it's certainly
correct; nevertheless, it does not show me the one outlier where the same
student had a significant problem.
So my question is... is there a way to include some form of statistical
adjustment to the query above that would take the outlier into
consideration.
I don't mean that I would want to show any record that is outside the 90 to
100 range. If the 10th score had been = 80% , I would not really need to
know about this.
In my example though, the deviation is so significant that I do want to have
something get my attention and "look deeper" into the individual exams.
Presuming I have the following variables:
X = Cumulative average of all scores
Y = Score of outlier
Z = Value of cumulative average minus lowest score
Z = X - Y
Z = 91 - 10
Z = 81
Again, I would need some help w/ figuring out "Z" and also maybe some ideas
as to best show "Z" (maybe a hidden textbox becomes visible). For instance,
"Z" becomes visible if Z > e.g. 50.
Also, what if I had more than one outlier? There certainly could have been
multiple outliers such as e.g. 10%, 25%, or even 45%.
I appreciate any help to solve this problem.
Thanks,
Tom
I need some help w/ adjusting the example query below to accomodate
"statistical outliers".
Expression: Round(Nz(Sum([TableName].[Score]),0)/[RecordCount],1)
Currently, I utilize the query to show a cumulutive average on a form.
Actually, I do not even show the averaging number (font color is same color
as background color) ... instead, for example, I simply show a "green
textbox" ("green" is based on scores between 90 to 100%)...
....and, this is where the problem lies...
Let's say I have 10 records (e.g. tracking "Student Exams") of which 9 exams
have a score of exactly 100% each.
However, the 10th score equals only 10% (whatever the cause might have
been).
As a result, the cumulutive average equals 91% (900 + 10/10=91). Based on
the overall average, the textbox is "green". Mathematically, it's certainly
correct; nevertheless, it does not show me the one outlier where the same
student had a significant problem.
So my question is... is there a way to include some form of statistical
adjustment to the query above that would take the outlier into
consideration.
I don't mean that I would want to show any record that is outside the 90 to
100 range. If the 10th score had been = 80% , I would not really need to
know about this.
In my example though, the deviation is so significant that I do want to have
something get my attention and "look deeper" into the individual exams.
Presuming I have the following variables:
X = Cumulative average of all scores
Y = Score of outlier
Z = Value of cumulative average minus lowest score
Z = X - Y
Z = 91 - 10
Z = 81
Again, I would need some help w/ figuring out "Z" and also maybe some ideas
as to best show "Z" (maybe a hidden textbox becomes visible). For instance,
"Z" becomes visible if Z > e.g. 50.
Also, what if I had more than one outlier? There certainly could have been
multiple outliers such as e.g. 10%, 25%, or even 45%.
I appreciate any help to solve this problem.
Thanks,
Tom