Help getting my syntax correct

**MMS6300** · 02-20-2009, 01:24 PM

I am calculating shipping charges in Excel 2007.

A1 is a drop down box with two options: Standard shipping and Priority Shipping.

I need to calculate based on two conditions:

If "Standard Shipping" is selected and the # of items (B1) is <=5, then the shipping charge (C1) = $5.00.

I need to nest this for 4 different values for B1 AND for Standard Shipping and Priority Shipping. I could also use two check boxes instead of the drop down if that would be better.

I just can't seem to get it right.

**NBVC** · 02-20-2009, 01:26 PM

Could you include all possibilities and the desired outcomes for each?

**MMS6300** · 02-20-2009, 01:35 PM

For Standard Shipping:

If B1 <= 15, then C1 = $5.00
If B1 >15, <=40, then C1 = $9.00
If B1 >40, <=60, then C1 = $11.00
If B1 >60, <=80, then c1 = $14.00

For Priority Shipping:

If B1 <= 4, then C1 = $5.00
If B1 >4, <=15, then C1 = $11.00
If B1 >15, <=30, then C1 = $17.00
If B1 >30, <=45, then c1 = $24.00

**NBVC** · 02-20-2009, 01:41 PM

Try:

Code:

=IF(A1="","",IF(A1="Standard Shipping",LOOKUP(B1,{0,16,41,61},{5,9,11,14}),LOOKUP(B1,{0,5,16,31},{5,11,17,24})))

note: this formula assumes that outside the upper bound of each range, then it takes the highest cost... if that is not the case, what do you want if the upperbound number in B1 exceeds the choices you have?

**MMS6300** · 02-20-2009, 01:57 PM

That worked for the standard shipping. How to I get it to change for the Priority shipping?

And..it only show a number .. 5.. instead of $5.00. Any fix for that?

Thank you thank you thank you!

**NBVC** · 02-20-2009, 02:10 PM

Originally Posted by MMS6300

That worked for the standard shipping. How to I get it to change for the Priority shipping?

And..it only show a number .. 5.. instead of $5.00. Any fix for that?

Thank you thank you thank you!

Try changing A1 to "Priority Shipping" you should see that it does work...

My formula assumes you have only the 2 choices... if Standard Shipping is selected it will do one calculation, otherwise it will do the other... unless A1 is blank...then it will return a blank.

The 5 to $5.00 is a matter of formatting your cell... Format it as Currency.. through Format|Cells >> Number tab.

**MMS6300** · 02-20-2009, 02:21 PM

I made it work by putting each type on shipping in a different cell (it shows both totals) then adding a check box to the cell.

THANK YOU!!

**NBVC** · 02-20-2009, 02:23 PM

I am not sure why it wouldn't have worked the original way..

If Cell A1 has the 2 options in a drop down, and B1 has the number of items, then C1 should come up with the correct corresponding dollar figure... using that formula...