create decile segments

[rbrem]

Hi,
I have a dataset with 3890 individual rows, sorted ascendingly by dollar amount. Each row is counted as one purchase. I want to create a formula that splits the data into deciles, or 10 equal groups of 389 purchases. For example, 1-389 is declie 1, 390-778 is decile 2, etc. I want to be able to write this formula once and copy it the whole way down. Thank you.

 

[Scott Huish]

Try this:

=PERCENTILE($A$1:$A$3890,ROWS($A$1:A1)/10)

Copy down.

 

[rbrem]

Thank you for your reply. The formula works for the first 10 rows, then I get a #NUM error for every row after that.

 

[shg]

How about ...

=INT((ROWS($A$1:A1) - 1) * 10 / ROWS($A$1:$A$3890))+1

... copied down.

 

[rbrem]

That worked, thank you for your help.

 

[thp510]

I have the same issue but with % instead of whole $ numbers and the function didn't work.

Column A has a person's name, column B has a % of their quota they hit. I'm trying to break the entire sales org into deciles. Any thoughts or suggestions?

 

[thp510]

Ah, never mind. I forgot to change the # of rows. Got it thanks!

 

[thp510]

Actually, another question... if I were to break it down into quartiles, do I just change the 10 to a 4 and that's it?

 

[Scott Huish]

There is a QUARTILE function.