verdon
Welcome to the Board


Joined: 12 Sep 2002
Posts: 2


Status: Offline

   

I have a list of numbers which are normally distributed. I want to assign a standard score to each number. eg for a number which was exactly on the avaerage I want a standard score of 100. For a number which is right at the bottom extreme I want a standard score of 69.

Any ideas?

_________________
Hope you can help

Joel Horowitz
Board Regular


Joined: 24 Aug 2002
Posts: 34


Status: Offline

   

I am not sure what you want exactly. A common way of giving a 'score' to a series of normally distributed numbers is to substract the average and divide by the standard deviation. i.e.
B1=(A1-average(A$1:A$50))/stdev(A$1:A$50)
and pulling down to B50
This gives a value of 0 to the average. On average, around 68% of the values will be between -1 and +1 and 95% between -2 and +2.

In your example, I can understand you want to set 100 as being the average, but what do you mean the 'bottom extreme', and why set it at 69?

quote:

---

On 2002-09-12 03:27, verdon wrote: I have a list of numbers which are normally distributed. I want to assign a standard score to each number. eg for a number which was exactly on the avaerage I want a standard score of 100. For a number which is right at the bottom extreme I want a standard score of 69. Any ideas?

---

verdon
Welcome to the Board


Joined: 12 Sep 2002
Posts: 2


Status: Offline

   

Thanks - yes I understand.

I thought that with a standard score table, the mean is 100, 68% of the values lie between 85 and 115, and 95 % of the values lie between 70 and 130. I wanted to ascribe a standard score, based on the above, to all my numbers. (Hence my request that the bottom 2.5% have a standard score of 69)

Any ideas?

Joel Horowitz
Board Regular


Joined: 24 Aug 2002
Posts: 34


Status: Offline

   

What you are speaking is then a normal distribution with average 100 and standard deviation 15. To get this score, you can just do
B1=100+15*(A1-average(A$1:A$50))/stdev(A$1:A$50)
and pull down the formula

quote:

---

On 2002-09-12 07:09, verdon wrote:
Thanks - yes I understand.

I thought that with a standard score table, the mean is 100, 68% of the values lie between 85 and 115, and 95 % of the values lie between 70 and 130. I wanted to ascribe a standard score, based on the above, to all my numbers. (Hence my request that the bottom 2.5% have a standard score of 69)

Any ideas?

---