Covert a Z score to a quintile and return a number 1 thru 5 based on the quintile.

[bob.walden]

I have converted a raw score to a z score in one column
In the next column I want to find the quintile score and return a 1 for 0-20%, 2 for 21-40%, 3 for 41-60%, 4 for 61-80% and 5 for 81-100%

Thanks, Bob

[Ron Coderre]

With
A1: a number between 0% and 100%, inclusive

This regular formula returns the quintile for that value
B1: =MAX(CEILING(A1/0.2,1),1)

Is that something you can work with?

[Kevin UK]

Could you not use a VLOOKUP
or Lookup:

=LOOKUP(A1,{0.00,0.21,0.41,0.61,0.81,0.81},{1,2,3,4,5})

[bob.walden]

It does not work in all the cells in the column.

[MrShorty]

meaning that you are having trouble copying either function down the column? Or meaning that it is returning unexpected values in some of the copied cells?

[ChemistB]

If you're using Kevin's there's a typo, should be

=LOOKUP(A1,{0.00,0.21,0.41,0.61,0.81,1.00},{1,2,3,4,5})
I also assume you mean you've used the z score to calculate an area or probability. The z score is the standard deviation (typically in a z score table between -3.5 to 3.5)

[Kevin UK]

Sorry about that.

Please Login or Register  to view this content.