Formula Recommendation

[stockopt]

I have referred to a number of your videos and your advice has helped me immensely. Can you please proved a recommendation for me? I am putting together an order form and to set up the shipping and handling. Here are the guidelines:

Total	Shipping
$0.00	$10.00
$99.99	$10.00
$100.00	10.00%
$1,999.99	10.00%
$2,000.00	9.50%
$4,999.90	9.50%
$5,000.00	$0.00

<colgroup class=""><col width="101" class="" style="width: 76pt;"><col width="141" class="" style="width: 106pt;"></colgroup><tbody class="">
</tbody>

I have a cell with the subtotal where I have used the Vlookup but the problem is with orders below $100. Can I do a Sumif and Vlookup combo? With the Vllookup, I have to multiply Shipping (%) with subtotal but I am unable to add $10 for all orders under $100. Thank you for your time.

 

[etaf]

can you add an IF()

IF( subtotal < $100.00 , Subtotal + 10 , Vlookup () )

 

[stockopt]

Thank you.

 

[etaf]

did that solve the question for you

 

[stockopt]

Wayne,

I wrote this formula: IF(subtotal <100,subtotal +10,VLOOKUP(subtotal ,Shipping,2)*subtotal ). If automatically add the the $10 but will the amount if over $100 it also the $10. It this correct formula. Thank you again

 

[stockopt]

I also have the cell formatted for % (subtotal)

 

[taikvei]

Wayne,

I wrote this formula: IF(subtotal <100,subtotal +10,VLOOKUP(subtotal ,Shipping,2)*subtotal ). If automatically add the the $10 but will the amount if over $100 it also the $10. It this correct formula. Thank you again

VLOOKUP(subtotal,shipping,2)*subtotal will only give you the shipping $, if you want the amount + shipping, you need subtotal + vlookup(subtotal,shipping,2)*subtotal.

Add ,1 at the end of your vlookup to make it vlookup(subtotal,shipping,2,1)
this will make it an approximate match, rather than an exact match