Frontpage
Forums
Tutorials
Members
Calendar
Subscription
Donate
Links
Contact 
Help
Posted 20 July 2006 - 08:46 PM
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
Page 1 of 1
>> Excel price list (formula help plz) <<
Rate Topic:
Back to top
#2
- Pharmassist
-
- Group: Super Moderator
- Posts: 1,667
- Joined: 31-July 04
- OS:Windows 7 x86
-
Country:
Posted 11 August 2006 - 06:02 PM
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...)
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...)
This post has been edited by spacesurfer: 11 August 2006 - 06:04 PM
Share this topic:
Page 1 of 1