Function IF and OR together

[Eli]

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?

 

[Michael]

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.

 

[smartin]

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?

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.

 

[joeu2004]

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 ----