# Thread: How to split values into intervals

1. ## How to split values into intervals

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. It can easily be handled with SUMPRODUCT. Post the details of the conditions, I will work out the formula for you.

3. 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. 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
•