How to split values into intervals

peterLast

New member
Joined
Sep 4, 2012
Messages
1
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
It can easily be handled with SUMPRODUCT. Post the details of the conditions, I will work out the formula for you.
 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 
Top