Increment a counter every ten rows

I have an Excel spreadsheet with more than 50,000 rows and want to add a column that effectively groups the data in sets of ten by assigning an increasing number to every ten rows heading downwards. To clarify, this is what I'm going for:

Col A Col B Decile
1 * * 1
2 * * 1
3 * * 1
4 * * 1
5 * * 1
6 * * 1
7 * * 1
8 * * 1
9 * * 1
10 * * 1
11 * * 2
12 * * 2
13 * * 2
14 * * 2
...
199 * * 19
200 * * 19
201 * * 20
...


The current way I have of doing this is to insert this function into the first entry of the Decile column and copy it down to the whole column:

=INT(COUNTA($A1:A$2)/10) + 1


which counts all the rows from A2 to the current row, divides by 10, floors to an integer and adds 1 to index beginning from 1.

The issue I'm running into is that for over 50,000 rows this takes Excel a godawful amount of time to calculate because for each row it has to count all the rows before it and then calculate a number (giving it a O(n^2) runtime if I'm remembering my algorithms lectures correctly). There must be a more efficient way of doing this and my lack of Excel expertise is preventing me from thinking of a different solution.

How would you tackle this?

1 Answer
1

This may offer some performance increase:

=INT(ROW($A1)/10) + 1


However, if you don't need these values to be dynamic. After filling out the first 10k or so, you can copy the entire column then paste as value. This will eliminate the need for excel to do continuous calculations.

Also you can change the calculation mode to manual in the "Formulas" tab in the Ribbon, under 'Calculation Options'. Then when you're done expanding the formula, hit calculate sheet. That way it doesn't continuously calculate every time you partially drag the formula to new cells.

That is definitely a lot faster, thanks!
– Matt Rosenthal
Nov 5 '15 at 22:21

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.