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

Check the answers in your original post.

It's best to stick with one thread per subject, so that others won't
duplicate efforts.

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"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

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