XL Price List (need formula plz)

Hi guys,

Just wondering if anyone could help with a formula I need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins.

Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time. Any help would be greatly appreciated.

Kind Regards,
Hayden

hi hayden,

i'm not a 100% sure if this is what you are trying to achieve, but here's what i gathered from your question:


say you have your supplier's price listed in column C of your XL worksheet, and say column D is where your margin (as a percentage) is. then, you could enter a formula like the one below in, let's say, cell D2:
=IF(C2<=20,3%,IF(AND(C2>20,C2<=100),2%,1%))
this formula basically creates 3 buckets:
cost price <= 20 -> returns 3%
cost price > 20 and <= 100 -> returns 2%
cost price > 100 -> returns 1%
this will work if the format in cell C2 is currency (with the dollar sign) as well.
you can use this kind of nested if upto 7 times...will that be enough times to cover all your cost price ranges?

let me know if it works or helps!