Help with IF function

[Guest]

Hi everyone. You guys are always a lot of help when I start hitting a wall!
Hope someone can help me again.

I am running XP Pro, SP2, and Office Professional 2003.

I am designing an invoice template for my company.
In cell A1, we enter either UPS or LTL (freight truck) for the shipping
method.
In cell A2, we enter the total amount of the order.
In cell A3, we enter the freight charge.

However, if the order can ship UPS, and the invoice total is more than $250,
the freight is N/C. If less than $250, we manually enter a freight amount.

If the order must ship freight truck (LTL), and the invoice total is more
than $400, the freight is N/C. If less than $400, we manually enter a freight
amount.

I need a formula for cell A3.

I want it to return "N/C" if A2>400 and A1="LTL", or if A2>250 and A1="UPS".
In all other situations I want it to return a blank ("") cell which will
prompt us to enter a freight amount.

Have I got you totally confused? I hope not.

Every time I've tried to build the formula I get most of it to work, but one
condition ends up returning FALSE in A3, which I don't want.

Thanks for any help you guys can give.

 

[Guest]

=if(or(and(a2>400,a1="LTL"),and(a2>250,a1="UPS")),"N/C","")
This combines the various situations into a single test so there's no need
to nest the if functions. --Bruce

 

[Bruno Campanini]

Hi everyone. You guys are always a lot of help when I start hitting a
wall! [...]
I need a formula for cell A3.

I want it to return "N/C" if A2>400 and A1="LTL", or if A2>250 and
A1="UPS".
In all other situations I want it to return a blank ("") cell which will
prompt us to enter a freight amount.

It should be:
=IF(OR(AND(A2>400,A1="LTL"),
AND(A2>250,A1="UPS")),"N/C","")


Bruno

 

[Guest]

Hi Jim

=IF(AND(A2>400,A1="LTL"),"N/C",IF(AND(A2>250,A1="UPS"),"N/C",""))

but what if A2 = 400 or 250 ??

IF this is the case change formula to:

=IF(AND(A2>=400,A1="LTL"),"N/C",IF(AND(A2>=250,A1="UPS"),"N/C",""))

HTH
Michael M

 

[Guest]

Hey thanks Bruce, Michael, and Bruno. You guys saved my butt (and my brain!).
--
Jim

Hi everyone. You guys are always a lot of help when I start hitting a
wall! [...]
I need a formula for cell A3.

I want it to return "N/C" if A2>400 and A1="LTL", or if A2>250 and
A1="UPS".
In all other situations I want it to return a blank ("") cell which will
prompt us to enter a freight amount.

It should be:
=IF(OR(AND(A2>400,A1="LTL"),
AND(A2>250,A1="UPS")),"N/C","")


Bruno