-
1. Re: Z Score Calculation - can anyone simplify?
Tracy RodgersJul 6, 2012 11:38 AM (in response to Emma Whyte)
Hi Emma,
If all you want is for the calculation to be one calculation versus three, then use the following:
((Avg([Satisfaction Score]))-(window_sum([Team Mean Score])/total(countd([Team])))) /
SQRT(window_sum(square([Team Mean Score]-[Mean of Population])) /
total(countd([Team])))
Hope this helps!
-Tracy
-
2. Re: Z Score Calculation - can anyone simplify?
Joe Mako Jul 11, 2012 4:01 PM (in response to Emma Whyte)Here is a simplification of your Z Score formula:
(
AVG([Satisfaction Score])
-
WINDOW_AVG(AVG([Satisfaction Score]))
)
/
WINDOW_STDEVP(AVG([Satisfaction Score]))
The difference from average divided by the standard deviation of the population.
-
3. Re: Z Score Calculation - can anyone simplify?
Emma Whyte Jul 12, 2012 2:21 AM (in response to Joe Mako)Hello,
Thanks very much Tracy and Joe! I will be working on these calculations tomorrow and next week so will get back to you on the results.
Thanks very much for the suggestions.
Emma
-
4. Re: Z Score Calculation - can anyone simplify?
Emma Whyte Jul 16, 2012 8:59 AM (in response to Joe Mako)Hello,
Joe - your calculation appears to work for questions where we are calculating Z Scores off a mean score - thanks!
The next step is to try and adapt this calculation for calculating Z Scores of a percentage, where we have to create the percentage first.
We record respondents answers to a numerical scale question in the database where answers can be 0-10. We then calculate the percentage of those who scored 0 &1, and those who scored 9 & 10 (Net Promoter Score). I am trying to incorporate working out the percentages within the Z Score Calculation:
(
(count([Promoters]) - count([Detractors]))
/
count([all question respondents])
) * 100
We have created 2 calculated fields (Promoters and Detractors) using IF statements.
Would anyone be able to adapt and integrate this calculation into a Z Score calculation to work out the AVG, Window_AVG and Window_StDevP?
Thanks very much,
Emma
-
5. Re: Z Score Calculation - can anyone simplify?
Emma Whyte Jul 17, 2012 2:11 AM (in response to Emma Whyte)Joe - would you be able to tell me what the difference is between WINDOW_STDEV and WINDOW_STDEVP?
I have tried both in the formula you gave me above, and they produce very similar results. I understand STDEVP uses a biased population, but the Help topics are a bit tricky to understand on this function. Would you be able to make it any clearer?
Thanks.
Emma
-
6. Re: Z Score Calculation - can anyone simplify?
Joe Mako Jul 17, 2012 6:38 PM (in response to Emma Whyte)As for the difference between stdev and stdevp, a quick Google search turned up:
Quote:
- STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."
- STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.
- STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."
-
7. Re: Z Score Calculation - can anyone simplify?
Joe Mako Jul 17, 2012 6:40 PM (in response to Emma Whyte)It would be best if you could supply a sample packaged workbook, that represents your situation, and the numbers you expect as a final result from the sample data provided. Thank you.