It can easily be handled with SUMPRODUCT. Post the details of the conditions, I will work out the formula for you.
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.
It can easily be handled with SUMPRODUCT. Post the details of the conditions, I will work out the formula for you.
Something like this may work for you:
A2 represents the Customer Total Purchase, the formula will provide the percent discount (format the formula cell as a percent).Code:=LOOKUP(A2,{0,1000,2000,3000},{0,0.05,0.07,0.1})
Or, this will provide the discounted purchase amount:
Code:=A2-LOOKUP(A2,{0,1000,2000,3000},{0,0.05,0.07,0.1})*A2
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
Bookmarks