Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
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 A2400 and A1="LTL", or if A2250 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. -- Jim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
=if(or(and(a2400,a1="LTL"),and(a2250,a1="UPS")), "N/C","")
This combines the various situations into a single test so there's no need to nest the if functions. --Bruce "Jim" wrote: 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 A2400 and A1="LTL", or if A2250 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. -- Jim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
"Jim" wrote in message
... 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 A2400 and A1="LTL", or if A2250 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(A2400,A1="LTL"), AND(A2250,A1="UPS")),"N/C","") Bruno |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
Hi Jim
=IF(AND(A2400,A1="LTL"),"N/C",IF(AND(A2250,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 "Jim" wrote: 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 A2400 and A1="LTL", or if A2250 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. -- Jim |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
Hey thanks Bruce, Michael, and Bruno. You guys saved my butt (and my brain!).
-- Jim "Bruno Campanini" wrote: "Jim" wrote in message ... 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 A2400 and A1="LTL", or if A2250 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(A2400,A1="LTL"), AND(A2250,A1="UPS")),"N/C","") Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |