[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

[Advertisements]

Hayden,
If you only have a few brackets of margin then an if statement is appropriate

example.
=IF(A9<20.01,1.44,IF(A9<100,1.35,IF(A9<200,1.26,1.2)))

the example assumes your cost price is in column A.
the formula returns 1.44 if your cost is less than $20.01
if the cost is more than $20 then it evaluates the next test
and returns 1.35 if the cost is <$100 (ie. > $20.00 and < $100.00)
it will continue to evaluate successive if statements
if none of the tests return a TRUE then the formula will return the last number 1.2


Hope this helps

DSM

[dsm]

Hayden,
If you only have a few brackets of margin then an if statement is appropriate

example.
=IF(A9<20.01,1.44,IF(A9<100,1.35,IF(A9<200,1.26,1.2)))

the example assumes your cost price is in column A.
the formula returns 1.44 if your cost is less than $20.01
if the cost is more than $20 then it evaluates the next test
and returns 1.35 if the cost is <$100 (ie. > $20.00 and < $100.00)
it will continue to evaluate successive if statements
if none of the tests return a TRUE then the formula will return the last number 1.2


Hope this helps

DSM

[homepcsolutions]

Thanks for the idea DSM! I tried it and it worked well, but then I did a bit more digging and come up with a VLOOKUP formula of:

=VLOOKUP(B17,$B$4:$C$11,2)

and created a table at the top with my price ranges and there appropiate margins. My first real attempt at VLOOKUP so I'm glad it worked~

Thanks for your help anyway much appreciated!