How to calculate discounts based on certain supplier codes/tags?

**rishim** · 08-06-2012, 03:25 AM

Hi everyone,

I am trying to write a formula that will automatically calculate NET pricing based on set discount rates for various suppliers.

The formula I have used is as follows:
=IF(B2=OR("AA","BB","CC","DD","EE","FF","GG"),G2*0.9)

Supplier tags are located in B2 and are two characters.

Basically, the subtotals are located in G2 and I want to formula to show the NET pricing (10% off).

I am trying to say that if the value in B2 is equal to any of the above mentioned tags, that the formula should reflect a 10% discount.

My apologies in advance if this post is not clear.

Thank you.

**Fotis1991** · 08-06-2012, 03:49 AM

Hi

Try

=IF(B2={"AA","BB","CC","DD","EE","FF","GG"},(G2*0,9),"")

---------- Post added at 08:49 AM ---------- Previous post was at 08:36 AM ----------

Apologize for this...

=IF(OR(B2={"AA","BB","CC","DD","EE","FF","GG"}),(G2*0.9),"")

**rishim** · 08-06-2012, 03:53 AM

Awesome... It worked. Thank you!

Would there be any way to expand this and use a single formula to say if HH, II, and so on = 15%, LL = 25%, MM = 30% (for example)? A few of my suppliers have higher discount levels.

The idea is to keep track of all my components ordered for the month in a single excel worksheet so it would be easier for me to check Statements the following month.

**Ace_XL** · 08-06-2012, 04:01 AM

You might be better of using a LOOKUP then. Make sure the suppliers are alhabetically listed in the array

=LOOKUP(A1,{"AA","BB","CC","DD","EE","FF","GG","HH","II","JJ",........},{0.9,0.9,0.9,0.9,0.9,0.9,0.9,0.15,0.15,0.15,......})