Standardized Points on Normal Distribution

[#1]

Dear All,

I have a survey data which needs to be standardized based on normal distribution curve on monthly basis. The attached sample has data for 3 months. Each employee has a class and duty. For example there are 316 employees under P class.In September only 268 of 316 employee had survey results. I need to find below values for each class:

• class average,
• class standard deviation,
• variance
• standardize point based on normal distribution for each employee

These values must be monthly basis. This solution will be similar the exam results on schools. The curve should be seperated levels as AA, BA, BB, CB, CC, DD, F based on standardized points.

After the calculation of above values I need to create a scorecard for each employee as attached one.

I hope that someone can help me for the solution.

Kind Regards,

Asim Akin

[swuehl]

Hi,

first of all, I would suggest creating a Month field in your data model, you probably need this anyway and some functions don't take calculated dimensions.

Then I would suggest that you look into avg(), stdev() functions, and I believe something like sqr(stdev(Point)) should give you variance.

Please find attached a rough sample of how I would start with creating some pivot tables for the numbers you want to calculate and for how I would calculate the normalized points. I am not sure how you want to transform these into your levels AA, BA, BB etc.

Before trying to design the scorecard, I would suggest to first concentrate on calculating the numbers, the more design part should be done if we've mastered the base part.

Hope this helps,

Stefan