Spliting a spreadsheet into quintiles and scoring each row

**Richard Harris** · 08-27-2010, 08:48 AM

I am looking for a formula to allow me split a spreadsheet's rows into quintiles i.e say I have 100 rows, then each quintile is 20 rows. I then want to allocate a score of 5 to each row in the first quintile, 4 to each row in the second etc. etc.
Any ideas as to how to achieve this would be very welcome indeed !.
Thanks

**darkyam** · 08-27-2010, 11:34 AM

Will the number of rows always be evenly divisible by 5? If not, how do you want the quintiles calculated? Round up, round down, or simple round?

If yes to the first question, perhaps =5-INT((ROW()-1)*5/COUNTA(A:A))

**Richard Harris** · 08-27-2010, 12:36 PM

Hi, Thanks for the quick response ! They will be divisible evenly by 5, except for the last which will take up any difference. I will try your suggesstion and see the result. Again thanks a million.

**lost angel** · 05-13-2014, 01:29 AM

thanks darkyam...so helpful..

**manik.nag** · 05-13-2015, 11:18 AM

can the result be done on 1 to 5 insted of 5 to 1

**FDibbins** · 05-13-2015, 01:07 PM

manik welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.