Divide the numbers by formula

[asyamonique]

[TABLE="width: 841"]
<tbody>[TR]
[TD][/TD]
[TD]First 1000 is 10 then plus every 1000 is 1 until 10000[/TD]
[TD]After 10.000 every 5.000 until 100.000[/TD]
[TD]After 100.000 every 10.000[/TD]
[/TR]
[TR]
[TD]1300[/TD]
[TD]11[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2300[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7,800[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17,800[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22,000[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]35,500[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]85,000[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]103,000[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]145,000[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 502"]
<tbody>[TR]
[TD][/TD]
[TD]COLUMN1[/TD]
[TD]COLUMN2[/TD]
[TD]COLUMN3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0-999[/TD]
[TD]1000-10000[/TD]
[TD]10000-1000000[/TD]
[/TR]
[TR]
[TD]700[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1400[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2500[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3500[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4400[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7800[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12000[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]75800[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]145000[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]200000[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]250000[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

 

[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