Using IF/WHEN formula for determining product shipping prices

**Batman777** · 06-12-2013, 11:12 PM

Hi Everybody,
Thanks in advance for your help! I am Super new to excel, but very excited about its possibilities. What a powerful tool!

How do i write an IF/THEN statement, or some kind of statement so that it compares a value (WEIGHT) to a range of values (shipping prices according to weight) and adds the correct amount (cost to ship) to another value (wholesale price), adds a percentage (profit margin), and yields a new value (MY PRICE)?

I have columns for WHOLESALE PRICE, WEIGHT, and MY PRICE. There are already values for wholesale price and product weight, and I am trying to determine my price for 500 products. I also have a chart for the Fedex prices per pound, but i'm not sure how to use it in the formula, ie. should I type in the prices in the same box as the weight range or keep those in separate boxes.

I need something like:
=IF(J1 (WEIGHT Column) is equal to or between 0-1 lbs, then add $7.95 to WHOLESALE price + 3% = MY PRICE,
IF J1 (WEIGHT Column) is equal to or between 1.1-3 lbs, then add $8.95 to WHOLESALE price + 3%= MY PRICE,
IF J1 (WEIGHT Column) is equal to or between 3.1-5 lbs, then add $9.95 to WHOLESALE price + 3%= MY PRICE,
IF J1 (WEIGHT Column) is equal to or between 5.1-7 lbs, then add $10.95 to WHOLESALE price + 3%= MY PRICE,
IF J1 (WEIGHT Column) is equal to or between 7.1-10 lbs, then add $11.95 to WHOLESALE price + 3%= MY PRICE.)

I hope that is not too complicated... but it seems like it is to me right now

...

Thanks guys!

**humdingaling** · 06-12-2013, 11:28 PM

your better off having a vlookup to a table of weights to assign the price, otherwise your going to end up with a very convoluted nested if statement which would be hard to maintain

**FDibbins** · 06-13-2013, 12:34 AM

Hi and welcome to the forum

What you want sounds fairly easy, as suggested, instead of a huge nested if statement, it will probably easier to create a table of values and then use a vlookup() statement to search for what you want.

If you upload a sample workbook, showing what you are working with, a sample answers along with how you arrived at them (dont upload any sensitive data), Im sure we can throw something together for you.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.