How to split values into intervals

[peterLast]

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]

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

[CheshireCat]

Something like this may work for you:

=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:

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

[Bob Phillips]

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