Points calculation with formula.

[asyamonique]

Good Day,
Is it possible to check below formula that giving me the results incorrectly.
12.000 point should be earned 12 tickets instead of 20 tickets.
Thank you in advance.

=IF(C2<1000,FLOOR(C2/100,1),IF(C2<10000,10+FLOOR((C2-1000)/1000,1),IF(C2<100000,20+FLOOR((C2-10000)/5000,1),38+FLOOR((C2-100000)/10000,1))))

TICKETING RULES

(100-1.000) every 100 is 1 Ticket

(1.000-10.000) every 1.000 is 1 Ticket.

(10.000-100.000) every 5.000 is 1 Ticket

(100.000 & Above) every 10.000 is 1 Ticket

 

[Dave Patton]

The rules are not clear.
You can review and extend the following.

T202310a.xlsm
	C	D
1
2	12000	12
3
3d

Cell Formulas
Range	Formula
D2	D2	=(C2>0)*MIN(INT(C2/1000),10)+(C2>10000)*(MIN((C2-10000)/1000,(100000-10000)/1000))

 

[Dave Patton]

How did you calculate 12?
What would be the the result for 110000?

T202310a.xlsm
	A	B	C	D	E
1
2			12000	19	19
3d

Cell Formulas
Range	Formula
D2	D2	=(C2>0)*(MIN(INT(C2/100),10))+(C2>1000)*(MIN(INT((C2-1000)/1000),9))+(C2>10000)*(MIN(INT((C2-10000)/5000),(100000-10000)/5000))+(C2>100000)*(C2-100000)/10000
E2	E2	=INT(SUMPRODUCT(--(C2>A4:A7),(C2-A4:A7),(C4:C7)))

 

[asyamonique]

It’s my bad
Actually 12,000 points should issue 20k
All is good
Tq

 

[asyamonique]

POINTS	TICKETS
100	1
200	2
300	3
400	4
500	5
600	6
700	7
800	8
900	9
1,000	10
2,000	11
3,000	12
4,000	13
5,000	14
6,000	15
7,000	16
8,000	17
9,000	18
10,000	19
15,000	20
20,000	21
25,000	22
30,000	23
35,000	24
40,000	25
45,000	26
50,000	27
55,000	28
60,000	29
65,000	30
70,000	31
75,000	32
80,000	33
85,000	34
90,000	35
95,000	36
100,000	37
110,000	38
120,000	39
130,000	40
140,000	41
150,000	42
160,000	43
170,000	44
180,000	45
190,000	46
200,000	47

 

[Dave Patton]

Thanks for the feedback.

T202310a.xlsm
	A	B	C	D		F
1
2			12000	19		19
3
3d

Cell Formulas
Range	Formula
D2	D2	=(C2>0)*(MIN(INT(C2/100),10))+(C2>1000)*(MIN(INT((C2-1000)/1000),9))+(C2>10000)*(MIN(INT((C2-10000)/5000),(100000-10000)/5000))+(C2>100000)*(C2-100000)/10000
F2	F2	=LOOKUP(C2,M2:N48)