Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

excel formula help (price price list margins)


  • Please log in to reply

#1
homepcsolutions

homepcsolutions

    New Member

  • Member
  • Pip
  • 2 posts
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
  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
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
  • 0

#3
homepcsolutions

homepcsolutions

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts
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! :whistling:
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP