# How to split values into intervals

#### peterLast

##### New member
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.

#### Bob Phillips

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

#### CheshireCat

##### New member
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``

#### Bob Phillips

##### Super Moderator
Staff member
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