Formula to create points

[asyamonique]

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

[COLOR=#333333]=IF(A1<=1000,INT(A1/100),10+INT((A1-1000)/1000))[/COLOR]

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"

 

[Legacy 143009]

Not elegant but does what you want:

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

 

[asyamonique]

Hi,
Thanks for your quick reply but the result is

[B]#NAME [/B]

which I couldn't figured out.

 

[Legacy 143009]

Ok, for example;

A1=345600

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

 

[asyamonique]

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

 

[asyamonique]

Its ok now,
Thanks a lot

 

[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

 

[Legacy 143009]

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

 

[asyamonique]

Thats great,
Could you please have a check below table what can be the alteration if the conditions are changes?
Thanks again.
[TABLE="width: 368"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]1000=[/TD]
[TD]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1400=[/TD]
[TD]11[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2400=[/TD]
[TD]12[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]5400=[/TD]
[TD]15[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]10000=[/TD]
[TD]19[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]15000=[/TD]
[TD]21[/TD]
[TD]Increase 1 for every 5000 after 10000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 143009]

Thats great,
Could you please have a check below table what can be the alteration if the conditions are changes?
Thanks again.
[TABLE="width: 368"]
<tbody>[TR]
[TD]1000=[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1400=[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2400=[/TD]
[TD]12[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]5400=[/TD]
[TD]15[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]10000=[/TD]
[TD]19[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]15000=[/TD]
[TD]21[/TD]
[TD]Increase 1 for every 5000 after 10000[/TD]
[/TR]
</tbody>[/TABLE]

Increase 1 for every 1000 after 1400 may be?!

Also here why you added2? Only 1 times 5000 added?!
[TABLE="width: 368"]
<tbody>[TR]
[TD]10000=[/TD]
[TD]19[/TD]
[TD]Increase 1 for every 1000 after 1000[/TD]
[/TR]
[TR]
[TD]15000=[/TD]
[TD]21[/TD]
[TD]Increase 1 for every 5000 after 10000[/TD]
[/TR]
</tbody>[/TABLE]