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:


[TABLE="width: 242"]
<colgroup class=""><col width="101" class="" style="width: 76pt;"><col width="141" class="" style="width: 106pt;"></colgroup><tbody class="">[TR]
[TD="class: xl64, width: 101"] Total[/TD]
[TD="class: xl65, width: 141"] Shipping[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$0.00[/TD]
[TD="class: xl66, align: right"]$10.00[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$99.99[/TD]
[TD="class: xl66, align: right"]$10.00[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$100.00[/TD]
[TD="class: xl68, align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$1,999.99[/TD]
[TD="class: xl68, align: right"]10.00%[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$2,000.00[/TD]
[TD="class: xl68, align: right"]9.50%[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$4,999.90[/TD]
[TD="class: xl68, align: right"]9.50%[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]$5,000.00[/TD]
[TD="class: xl66, align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]


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