Different discount for different products amount

Hi all and everybody,

I need to create products order form in MS Excel and enable few different discounts for each product depending on ordered quantity. Let's say, if customer orders Product #1 5 pcs, he/she will see total sum with 20% discount. If customer orders Product #1 10 pcs, he/she will see total sum with 25% discount etc.

I'm just wondering if there any formula to do this in Excel?

Thank you in advance.

A.

Hi Glenn,

thank you for really prompt reply.

So, here you are:

Product #1

Regular price: EUR 50.-
Orders up to 5 pcs: 20% discount;
Orders 5-9 pcs: 25% discount;
Orders 10-19 pcs: 30% discount;
Orders 20 pcs and more: 35% discount.

Hope it helps.

Thanks.

Glenn, thank you! Will take a look and let you know how it works.

Thank you again!

Glenn,

works like a charm! Thank you indeed!

I have one more question re this formula:

=IF(G3<1,I3*0.8,IF(G3<5,I3*0.75,IF(G3<10,I3*0.7,IF(G3<20,I3*0.65))))

Everything works fine until there is 20 or more in G3 cell. Where is the mistake?

Thank you in advance.

Glenn,

thank you. One more question. Can I use one formula (like a posted in previous post) without "box" on the right side (Threshold, Minimum etc.) If there are about 50 products with different prices, I have to copy this "box" for each product. Perhaps I can use one formula instead?

Thanks in advance.

Yes, correct, discount rates are the same. Product prices are changing.

Glenn,

thank you! You _are_ an Expert. Works like a charm. Thank you indeed.