Divide the numbers by formula

[asyamonique]

	First 1000 is 10 then plus every 1000 is 1 until 10000	After 10.000 every 5.000 until 100.000	After 100.000 every 10.000
1300	11	0	0
2300	12	0	0
7,800	17	0	0
17,800	20	1	0
22,000	20	2	0
35,500	20	5	0
85,000	20	15	0
103,000	20	18	0
145,000	20	18	4

<tbody>
</tbody>

Good day,
What would be the formulas from the cells B2-C2-D2 all the way down to find the results with the related information from column A and above that cells.
Many Thanks

 

[StephenCrump]

B2: =10+MEDIAN(0,10,INT(A2/1000))
C2: =MEDIAN(0,18,INT((A2-10000)/5000))
D2: =MAX(0,INT((A2-100000)/10000))

Book1
	A	B	C	D
1		First 1000 is 10 then plus every 1000 is 1 until 10000	After 10.000 every 5.000 until 100.000	After 100.000 every 10.000
2	500	10	0	0
3	1300	11	0	0
4	2300	12	0	0
5	7,800	17	0	0
6	17,800	20	1	0
7	22,000	20	2	0
8	35,500	20	5	0
9	85,000	20	15	0
10	103,000	20	18	0
11	145,000	20	18	4
Sheet1

Your first condition is ambiguous - did you want B2 = 10 as shown? Or should this be zero?

 

[asyamonique]

Hi,
No did not wanted.
Thanks a lot

 

[asyamonique]

Hi Stephen,
Is it possible create one more coulumn which will count only 100's till 1000 like:
16800= 8 hundreds (0-999)

 

[StephenCrump]

Hi Stephen,
Is it possible create one more coulumn which will count only 100's till 1000 like:
16800= 8 hundreds (0-999)

Do you mean like this:

B1: =FLOOR(MOD(A1,1000)/100,1)

Book1
	A	B
1	99	0
2	101	1
3	220	2
4	899	8
5	901	9
6	999	9
7	1,099	0
8	1,101	1
9	1,600	6
10	2,799	7
11	16,800	8
12	20,999	9
Sheet1

 

[asyamonique]

Hi,
The whole table is given below is the sample what I needed for each column.
Many Thanks

	COLUMN1	COLUMN2	COLUMN3
	0-999	1000-10000	10000-1000000
700	7	0	0
1000	9	1	0
1400	9	1	0
2500	9	2	0
3500	9	3	0
4400	9	4	0
7800	9	7	0
12000	9	10	1
75800	9	10	7
145000	9	10	14
200000	9	10	20
250000	9	10	25

<tbody>
</tbody>

 

[StephenCrump]

B2: =MIN(9,INT(A2/100))
C2: =MIN(10,INT(A2/1000))
D2: =MIN(25,INT(A2/10000))

Book1
	A	B	C	D
1		0-999	1000-10000	10000-1000000
2	700	7	0	0
3	1,000	9	1	0
4	1,400	9	1	0
5	2,500	9	2	0
6	3,500	9	3	0
7	4,400	9	4	0
8	7,800	9	7	0
9	12,000	9	10	1
10	75,800	9	10	7
11	145,000	9	10	14
12	200,000	9	10	20
13	250,000	9	10	25
Sheet1

 

[asyamonique]

Thanks a lot