Converting percentiles to quintiles

[agbiggs]

This sounds silly, but I have a column of data with percentile values
(i.e., 1 to 100) and I'd like to create a matching column showing what
quintile each one is in. Eg, if the percentile value is between 0-20
it's in the first quintile, 20-40, in the second, etc.).

Is there any easy way to do this other than a big nested IF statement?
If so, thanks!

 

[Pete_UK]

Assuming your data starts in A1, put this formula in B1:

=INT((A1-1)/20)+1

and copy down.

Hope this helps.

Pete

 

[agbiggs]

Assuming your data starts in A1, put this formula in B1:

=INT((A1-1)/20)+1

and copy down.

Hope this helps.

Pete

Thanks very much -- that works perfectly. I also tried it using the
TRUNC function, which seems to work. Thanks again.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete