Formula or vba code to calculate the values between two numbers with criteria

[asyamonique]

POINTS	TICKETS
7,030	17
6,711	16
4,709	14
3,651	13
2,990	12
2,970	12
2,580	12
800	8
300	3

<colgroup><col><col></colgroup><tbody>
</tbody>

Good Day,
I would like to ask you in regards the given table above which I like to get the result when it comes to calculate promo ticket amounts depands on the points the customers earned.
Calculation simply will be like that:
If they have 100 to 1000point they will earn 10 tickets.
ıf above 1000 then every 1000 will be additionaly 1 ticket.

Many Thanks

 

[FDibbins]

If they have 100 to 1000point they will earn 10 tickets.
ıf above 1000 then every 1000 will be additionaly 1 ticket.

Based on your sample answers, it seems you want 1 ticket for every 100 points, up to 1000 point/10 tickets?

 

[FDibbins]

Assuming a "yes" for my question above, it also looks to me like you have added 1 extra point for those over 1000?

7,030 17
6,711 16
4,709 14

Based on those...
7030 -1000 = 6030, so that would be 10 + 6?
UNLESS it is 1000 points or part-thereof?

This formula works for adding a ticket for every full 1000
=IF(A1<=1000,INT(A1/100),10+INT((A1-1000)/1000))

 

[asyamonique]

Many thanks

 

[FDibbins]

Happy to help

 

[asyamonique]

Hi,
Is it possible to add more function including 10.000 and folds, into that formula like below?
Between 100 to 1000 = 10 tickets
Every 1.000 = 1 ticket
Every 10.0000 = 1 ticket

Sample:
900point = No ticket
1500point = 10 ticket
7400 points= 16tickets
35000 points= 17tickets

Thanks

 

[asyamonique]

Correction,
Sorry!
900point = 9 tickets
1500point = 15 tickets
7400 points= 20 tickets
35000 points= 44 tickets