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

[asyamonique]

[TABLE="width: 204"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]POINTS[/TD]
[TD]TICKETS[/TD]
[/TR]
[TR]
[TD]7,030[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]6,711[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]4,709[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]3,651[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2,990[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2,970[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2,580[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



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