I would get rid of that long formula and create a 2 column table for the
amounts and their corresponding charges.
See this screencap:
http://img185.imageshack.us/img185/8459/lookuptz5.jpg
For the last amount of the table (300.01) I use a formula to calculate the
corresponding shipping charge.
Then the lookup formula becomes much more manageable. Also, when
prices/shipping charges go up (and you know they will!) then all you have to
do is update the table rather than messing around and rewriting the formula.
--
Biff
Microsoft Excel MVP
"Snake_Plisken" wrote in message
...
I have created an Excel Order form. After the Subtotal and Tax have been
calculated, I want the form to automatically put in the handling charges.
Formula works beautifully until I need it to return correct result for
orders
above $300.
I would like every order that is over $300 to have handling charges equal
to
7% of the order.
My formula is:
=LOOKUP(E39+E40,{0,10.01,25.01,40.01,55.01,70.01,8 5.01,100.01,150.01,200.01,300.01;3,5,6,7,8,9,10,11 ,13,14,IF(E39+E40300,(E39+E40)*7%}))
Can I place an IF statement within a LOOKUP? After the error, Excel
highlights the *7%.
Please help and Thanks in advance