Thanks:  0
Likes:  0

# Thread: Formula to create points

1. ## Formula to create points

Good Day,
This formula works for adding a ticket for every full 1000

Code:
`=IF(A1<=1000,INT(A1/100),10+INT((A1-1000)/1000))`
Is it possible to change above formula base on below rules?
Many Thanks

Every 100= 1point
Between 100 to 1.000= 10point
Every 1,000= 1point
Between 1.000 to 10.000= 10point
Every 10.000= 1point
Between 10.000 to 100.000= 10point
Every 100.000= 1point

Sample:
345600= "3+4+5+6+30= 48 point"
54700= "5+4+7+20= 36 point"
6700= "6+7+10= 23 point"

2. ## Re: Formula to create points

Not elegant but does what you want:

=SUMPRODUCT(1*MID(A1/100,ROW(INDIRECT("1:"&LEN(A1/100))),1))+((LEN(A1/100)-1)*10)

3. ## Re: Formula to create points

Hi,
Code:
`#NAME `
which I couldn't figured out.

4. ## Re: Formula to create points

Ok, for example;

A1=345600

When you paste this formula to B1, it works for me.

5. ## Re: Formula to create points

Will try on my different excel and will keep you updated.
Thanks again

Its ok now,
Thanks a lot

7. ## Re: Formula to create points

Ohh sorry, but if the numbers with deciminal the result shown as #VALUE !
Is teher any solution for that error?
If the number 345600 the result corret but if 345600,4 it gives error!
Thanks again

8. ## Re: Formula to create points

Originally Posted by asyamonique
Ohh sorry, but if the numbers with deciminal the result shown as #VALUE !
Is teher any solution for that error?
If the number 345600 the result corret but if 345600,4 it gives error!
Thanks again
Thanks for the feedback. Maybe you can wrap the value into integer function in the first place:
=SUMPRODUCT(1*MID(INT(A1/100),ROW(INDIRECT("1:"&LEN(INT(A1/100)))),1))+(LEN(INT(A1/100))-1)*10

9. ## Re: Formula to create points

Thats great,
Could you please have a check below table what can be the alteration if the conditions are changes?
Thanks again.
 1000= 10 1400= 11 2400= 12 Increase 1 for every 1000 after 1000 5400= 15 Increase 1 for every 1000 after 1000 10000= 19 Increase 1 for every 1000 after 1000 15000= 21 Increase 1 for every 5000 after 10000

10. ## Re: Formula to create points

Originally Posted by asyamonique
Thats great,
Could you please have a check below table what can be the alteration if the conditions are changes?
Thanks again.
 1000= 10 1400= 11 2400= 12 Increase 1 for every 1000 after 1000 5400= 15 Increase 1 for every 1000 after 1000 10000= 19 Increase 1 for every 1000 after 1000 15000= 21 Increase 1 for every 5000 after 10000
Increase 1 for every 1000 after 1400 may be?!

 10000= 19 Increase 1 for every 1000 after 1000 15000= 21 Increase 1 for every 5000 after 10000