Create Excel order forms that automatically compute shipping charges

by Mary Ann Richardson | Feb 20, 2007 8:00:00 AM

Tags: Microsoft Office, Mary Ann Richardson, order form, Microsoft Excel, Microsoft Office Suite Excel Tips Newsletter

Takeaway: Online order forms are great for figuring out shipping charges—as long as you're working with the right data. Mary Ann Richardson tells how to set up Excel so it automatically calculates the right shipping charges based on the bottom line.

Online order forms should be able to perform all calculations for the customer—even those based on a variable amount. For example, the form should automatically calculate the shipping charges from the purchase price. To do this, you will need to create a table of shipping charges in Excel and then create a formula for looking up the correct charges for the customer. Follow these steps to create the lookup table:

1. Click in an area of your spreadsheet outside your order form (for example, M1.)
2. Enter Total Purchase in M1.
3. Enter Shipping Charge in N1.
4. Enter 1 in M2 and 4.99 in N2.
5. Enter 15.01 in M3 and 6.99 in N3.
6. Enter 25.01 in M4 and 8.99 in N4.
7. Enter 40.01 in M5 and 9.99 in N5.
8. Enter 50.01 in M6 and 10.99 in N6.
9. Enter 75.01 in M7 and 12.99 in N7.
10. Select M1:N7.
11. Click in the Name box in the formula bar and type Shipandprocess.

Now that you've created the table, you can add a vlookup formula to your order form that will automatically fill in the correct shipping charge based on the order total. For example, say cell K9 of your invoice sums up the order total, and K10 shows shipping and processing charges. To have Excel automatically enter the charges, click K10 and enter the following formula:

=VLOOKUP(K9,Shipandprocess,2,TRUE)

If the order total is $55.60, the shipping charge will be $10.99; if the order table is $100.00, the charge will be $12.99. Note that if shipping charges change, you simply have to change the values in the table; the formula remains the same.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

Use Weights too. Navyman | 02/21/07

For UPS , FedEx mansoorm@... | 03/14/07