Assign numbers in block of 10 starting from lower to higher

[motilulla]

Hello,</SPAN></SPAN>

In the column "D" I got the numbers disordered, they can be from 0 to 3000 max. </SPAN></SPAN>
I need to assign them in column "E" in blocks of 10 (1-10, 11-20...etc.) starting from lower to higher as per example shown below.</SPAN></SPAN>

	A	B	C	D	E
1
2
3
4				Data	Result
5				67	6
6				98	8
7				112	9
8				33	3
9				29	3
10				42	4
11				113	9
12				20	2
13				125	10
14				19	2
15				81	6
16				111	9
17				21	2
18				61	5
19				173	12
20				48	4
21				1	1
22				40	4
23				8	1
24				46	4
25				130	10
26				51	4
27				62	5
28				134	10
29				15	2
30				92	8
31				157	11
32				135	11
33				38	3
34				399	15
35				74	6
36				0	0
37				6	1
38				26	3
39				25	3
40				18	2
41				65	6
42				200	12
43				7	1
44				83	7
45				220	12
46				16	2
47				400	15
48				10	1
49				106	8
50				14	2
51				254	13
52				2	1
53				53	4
54				3	1
55				75	6
56				90	7
57				24	3
58				261	13
59				108	9
60				559	16
61				158	12
62				70	6
63				236	13
64				117	9
65				86	7
66				17	2
67				68	6
68				59	5
69				84	7
70				174	12
71				340	14
72				85	7
73				82	7
74				231	12
75				104	8
76				116	9
77				5	1
78				39	4
79				28	3
80				323	14
81				102	8
82				394	14
83				123	10
84				54	5
85				60	5
86				11	1
87				110	9
88				326	14
89				144	11
90				169	12
91				23	3
92				120	10
93				428	15
94				141	11
95				56	5
96				339	14

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0 "><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Hello, may be I clear it a bit more assign block of (1 to 10, number 1), block of (11 to 20, number 2)... and so on</SPAN></SPAN>

Regards,</SPAN>
Moti</SPAN></SPAN>

 

[Fluff]

I cannot figure out what you are trying to do, as your results don't make any sense to me.
Are you looking for numbers 1-10 to be 1, 11-20 to be 2 etc regardless of how many numbers exist in those blocks?
Or do you want the 10 smallest numbers (ignoring 0) to be 1, the next batch of 10 numbers to be 2 etc?

 

[motilulla]

I cannot figure out what you are trying to do, as your results don't make any sense to me.
Are you looking for numbers 1-10 to be 1, 11-20 to be 2 etc regardless of how many numbers exist in those blocks?
Or do you want the 10 smallest numbers (ignoring 0) to be 1, the next batch of 10 numbers to be 2 etc?

Hello Fluff, yes it is bit confusing I got in the column "D" 0 to max 3000 number without any repetitions, so as you guess (ignoring the 0 it is correct) and looking for numbers 1-10 to be 1, 11-20 to be 2 and 21-30 to be 3... and so on this is exactly what I want the result in the column "E" </SPAN></SPAN>

Thank you</SPAN></SPAN>


Regards,</SPAN>
Moti</SPAN></SPAN>

 

[shg]

In E5,

=INT((D5+9)/10)

 

[motilulla]

In E5,

=INT((D5+9)/10)

Wow shg, what a beautiful formula spot on worked as wanted query solved!</SPAN></SPAN>

Thank you for your time and help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[shg]

You're welcome.