Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF and OR together
I have 5 tanks (100/1001, 100/1002, 100/1003, 100/1004, 100/1005) which we
send product into each one, not necessarily on the same range. The formula has the tank number and the tank size. However, it has 3 different ways to write these tanks on the cells. 100/1001, 100-1001 or 01 If my tank is 100/1001, the amount of product from that tank has to be multiplied by the tank size, if not, the function will look for other tank, and go on. Simplifying: =IF(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18-AN19)*10380, €ś other tank€ť) The formula I came with is: ( it is not working) =IF(OR(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18- AN19)*10380, IF(OR(AN17="100/1005",AN17="100-1005",AN17="05"),(AN18- AN19)*9636, IF(OR(AN17="100/1002",AN17="100-1002",AN17="02"),(AN18-AN19)*2415+4141, IF(OR(AN17="100/1003",AN17="100-1003",AN17="03"),(AN18-AN19)*988+1326, IF(OR(AN17="100/1004",AN17="100-1004",AN17="04"),(AN18-AN19)*430.9, "ERROR"))))) I dont know if the OR function works with 3 logical. Can somebody assist me please? -- Thanks......... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF and OR together
The formula works fine in my testing environment with every single variation.
However, if you have "05 as opposed to '05 then the result is an error. Just make sure the cell where the data comes in, is formatted as text, and you should get the appropriate results. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Eli" wrote: I have 5 tanks (100/1001, 100/1002, 100/1003, 100/1004, 100/1005) which we send product into each one, not necessarily on the same range. The formula has the tank number and the tank size. However, it has 3 different ways to write these tanks on the cells. 100/1001, 100-1001 or 01 If my tank is 100/1001, the amount of product from that tank has to be multiplied by the tank size, if not, the function will look for other tank, and go on. Simplifying: =IF(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18-AN19)*10380, €ś other tank€ť) The formula I came with is: ( it is not working) =IF(OR(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18- AN19)*10380, IF(OR(AN17="100/1005",AN17="100-1005",AN17="05"),(AN18- AN19)*9636, IF(OR(AN17="100/1002",AN17="100-1002",AN17="02"),(AN18-AN19)*2415+4141, IF(OR(AN17="100/1003",AN17="100-1003",AN17="03"),(AN18-AN19)*988+1326, IF(OR(AN17="100/1004",AN17="100-1004",AN17="04"),(AN18-AN19)*430.9, "ERROR"))))) I dont know if the OR function works with 3 logical. Can somebody assist me please? -- Thanks......... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF and OR together
Eli wrote:
I have 5 tanks (100/1001, 100/1002, 100/1003, 100/1004, 100/1005) which we send product into each one, not necessarily on the same range. The formula has the tank number and the tank size. However, it has 3 different ways to write these tanks on the cells. 100/1001, 100-1001 or 01 If my tank is 100/1001, the amount of product from that tank has to be multiplied by the tank size, if not, the function will look for other tank, and go on. Simplifying: =IF(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18-AN19)*10380, €ś other tank€ť) The formula I came with is: ( it is not working) =IF(OR(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18- AN19)*10380, IF(OR(AN17="100/1005",AN17="100-1005",AN17="05"),(AN18- AN19)*9636, IF(OR(AN17="100/1002",AN17="100-1002",AN17="02"),(AN18-AN19)*2415+4141, IF(OR(AN17="100/1003",AN17="100-1003",AN17="03"),(AN18-AN19)*988+1326, IF(OR(AN17="100/1004",AN17="100-1004",AN17="04"),(AN18-AN19)*430.9, "ERROR"))))) I dont know if the OR function works with 3 logical. Can somebody assist me please? OR will work with 3 or more, but with so many conditions this approach gets nightmarish quickly, as you have seen. You could simply this a lot if you normalize the tank names to the ordered list 1,2,3,4,5. Then you could use the CHOOSE function instead of nested IFs. E.g., make a table like this somewhere, mapping all the descriptions of the same tank to the same number: col A (!text!) col B (number) 100/1001 1 100-1001 1 01 1 100/1005 2 100-1005 2 05 2 etc. In a spare cell, say AO17, look up AN17 in the normalized table: =VLOOKUP(AN17,A:B,2,FALSE) Now you can use CHOOSE to pick the appropriate formula based on the lookup result: =CHOOSE(AO17,(AN18-AN19)*10380,(AN18-AN19)*9636,etc...) This will not handle the "ERROR" condition as-is, but that shouldn't be too hard to figure out, if needed. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF and OR together
On Jan 7, 3:22 pm, Eli wrote:
The formula I came with is: ( it is not working) =IF(OR(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18- AN19)*10380, [....elided....] I have not bothered to parse or try your formula myself. But the following thoughts might help you. First, take a careful look at AN17 to be sure that its value is type "text". The safest thing to do is to be that the contents of AN17 is prefixed with an apostrophe ('). That will be sure that Excel does not try to interpret the content differently. Second, if you are in control of the contents of AN17 such that you do not need any internal error checking, you might consider the following simplification: =(AN18-AN19) * choose(right(AN17,2), 10380, 2415+4141, 988+1326, 430.9, 9636) ----- original posting ---- On Jan 7, 3:22*pm, Eli wrote: I have 5 tanks (100/1001, 100/1002, 100/1003, 100/1004, 100/1005) which we send product into each one, not necessarily on the same range. *The formula has the tank number and the tank size. However, it has 3 different ways to write these tanks on the cells. * 100/1001, 100-1001 or 01 If my tank is 100/1001, the amount of product from that tank has to be multiplied by the tank size, if not, the function will look for other tank, and go on. Simplifying: =IF(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18-AN19)*10380, “ other tank”) The formula I came with is: * *( it is not working) =IF(OR(AN17="100/1001",AN17="100-1001",AN17="01"),(AN18- AN19)*10380, IF(OR(AN17="100/1005",AN17="100-1005",AN17="05"),(AN18- AN19)*9636, IF(OR(AN17="100/1002",AN17="100-1002",AN17="02"),(AN18-AN19)*2415+4141, IF(OR(AN17="100/1003",AN17="100-1003",AN17="03"),(AN18-AN19)*988+1326, IF(OR(AN17="100/1004",AN17="100-1004",AN17="04"),(AN18-AN19)*430.9, "ERROR"))))) I don’t know if the OR function works with 3 logical. Can somebody assist me please? -- Thanks......... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |