>> Excel price list (formula help plz) <<

[homepcsolutions]

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

[spacesurfer]

Produce Cost Margin After Margin

A B C

1 $50 =if(0<=a1<=20,1.44,if(21<=a1<=100,1.26,...) =(a1*b1)

formatting is messed up above but $50 = a1, formula is in B1, and =(a1*b1) is in C1.

Use the if formula. The syntax is =if(condition,true,false).

You'll have to nest as many if's as there are your margins.

=if(x1<=a1<=x2,margin1,if(y1<=a1<=y2,margin2,if(z1<=a1<=z2,margin3...)

Edited August 12, 2006 by spacesurfer