Formula Recommendation

stockopt

New Member
Joined
Jan 7, 2019
Messages
4
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.0010.00%
$1,999.9910.00%
$2,000.009.50%
$4,999.909.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

Well-known Member
Joined
Oct 24, 2012
Messages
3,428
can you add an IF()

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

stockopt

New Member
Joined
Jan 7, 2019
Messages
4
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
 

taikvei

New Member
Joined
Nov 29, 2018
Messages
14
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
 
Last edited:

Forum statistics

Threads
1,085,306
Messages
5,382,834
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top