Results 1 to 4 of 4

Thread: How to split values into intervals

  1. #1

    How to split values into intervals



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,


    in my table I have customers and their total spend. I need to assign to customers a discount. The amount of discount depends on the amount of spending. For example, <1000 is a discount of 0%, from 1001 to 2000 is a discount of 5%, from 2001 to 3000 is 7%, ...


    I have tried IF function, but it is too complicated, because there is a lot of conditions. Is there some other solution?

    Thanks.

  2. #2
    It can easily be handled with SUMPRODUCT. Post the details of the conditions, I will work out the formula for you.

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    Something like this may work for you:

    Code:
    =LOOKUP(A2,{0,1000,2000,3000},{0,0.05,0.07,0.1})
    A2 represents the Customer Total Purchase, the formula will provide the percent discount (format the formula cell as a percent).

    Or, this will provide the discounted purchase amount:

    Code:
    =A2-LOOKUP(A2,{0,1000,2000,3000},{0,0.05,0.07,0.1})*A2

  4. #4
    I was thinking more like this

    =SUMPRODUCT(--(A3>{0;1000;2000;3000;40000}), (A3-{0;1000;2000;3000;40000}), {0;0.05;0.02;0.02;0.03})

    to handle the variable rates

Posting Permissions

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