Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Formula to create points

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Location
    Hong Kong
    Posts
    981
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    661
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    Last edited by Flashbond; Mar 20th, 2018 at 06:13 AM.

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Location
    Hong Kong
    Posts
    981
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to create points

    Hi,
    Thanks for your quick reply but the result is
    Code:
    #NAME 
    which I couldn't figured out.
    Last edited by asyamonique; Mar 20th, 2018 at 06:38 AM.

  4. #4
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    661
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to create points

    Ok, for example;

    A1=345600

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

  5. #5
    Board Regular
    Join Date
    Jan 2008
    Location
    Hong Kong
    Posts
    981
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to create points

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

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Location
    Hong Kong
    Posts
    981
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to create points

    Its ok now,
    Thanks a lot

  7. #7
    Board Regular
    Join Date
    Jan 2008
    Location
    Hong Kong
    Posts
    981
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    661
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to create points

    Quote Originally Posted by asyamonique View Post
    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
    Last edited by Flashbond; Mar 21st, 2018 at 12:55 AM.

  9. #9
    Board Regular
    Join Date
    Jan 2008
    Location
    Hong Kong
    Posts
    981
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    661
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to create points

    Quote Originally Posted by asyamonique View Post
    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?!

    Also here why you added2? Only 1 times 5000 added?!
    10000= 19 Increase 1 for every 1000 after 1000
    15000= 21 Increase 1 for every 5000 after 10000
    Last edited by Flashbond; Mar 21st, 2018 at 04:36 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •