IF Statement on multiple levels

[ljochmann]

I am trying to build a formula based on the following information.
IF cell E = (a list of specific numbers) then true would be 0 and fals
would be (BH-AT)*AF. I also would like the formula to do a secondary I
statement that if "VEN-NUM" or "STARTUP" is in Cell H then 0 otherwis
the same formula and finally IF "ES" or "TPG" which is only part of
part number is in cell H then 0 otherwaise same formula.
Here is what I have thus far. I am thinking there is an easier way and
do not know how to do step 3.

=IF(H5="VEN-NUM",0,IF(H5="STARTUP",0,IF(E5=106547,0,IF(E5=112565,0,IF(E5=133256,0,IF(BF5<1,0,(BH5-$AT5)*BF5))))))

1. IF E5 = 106547 or 112565 or 133256 then 0... If False the
(BH5-AT5)*BF5
2. IF H5 = "VEN-NUM" or "STARTUP" then 0... If False then (BH5-AT5)*BF5
3. IF H5 contains "ES" or "TPG" then 0... If False then (BH5-AT5)*BF5

Thanks
Lynnett

 

[Claus Busch]

Hi Lynette,

Am Mon, 3 Mar 2014 15:22:43 +0000 schrieb ljochmann:

1. IF E5 = 106547 or 112565 or 133256 then 0... If False then
(BH5-AT5)*BF5
2. IF H5 = "VEN-NUM" or "STARTUP" then 0... If False then (BH5-AT5)*BF5
3. IF H5 contains "ES" or "TPG" then 0... If False then (BH5-AT5)*BF5

I don't really understand your question :-(
If E56 is False then always (BH5-AT5)*BF5?
Or then testing H5 and only if both are False then (BH5-AT5)*BF5?

If E5 AND H5 must be false for (BH5-AT5)*BF5
try:
=IF(OR(E5={106547;112565;133256}),0,IF(OR(H5={"VEN-NUM";"STARTUP";"ES";"TPG"}),0,(BH5-AT5)*BF5))
Else try:
=MAX(IF(OR(E5={106547;112565;133256}),0,(BH5-AT5)*BF5),IF(OR(H5={"VEN-NUM";"STARTUP";"ES";"TPG"}),0,(BH5-AT5)*BF5))
if the results always are positive


Regards
Claus B.

 

[joeu2004]

I am thinking there is an easier way [....]
=IF(H5="VEN-NUM",0,IF(H5="STARTUP",0,IF(E5=106547,0,
IF(E5=112565,0,IF(E5=133256,0,IF(BF5<1,0,(BH5-$AT5)*BF5))))))

For what you have so far, you could write instead:

=IF(OR(H5={"VEN-NUM","STARTUP"},E5={106547,112565,133256},BF5<1),
0,(BH5-$AT5)*BF5)

Be careful to distinguish curly braces after H5= and E5= v. parenthesis.
That might be difficult to see in some fonts. It is best to copy-and-paste
the alternative above.

However....

1. IF E5 = 106547 or 112565 or 133256 then 0...
If False then (BH5-AT5)*BF5
2. IF H5 = "VEN-NUM" or "STARTUP" then 0...
If False then (BH5-AT5)*BF5

You original expression does not implement that logic. Instead, your
original expression implements the following logic:

1. If H5 = "VEN-NUM" or "STARTUP", return 0
2. Else if E5 = 106547 or 112565 or 133256, return 0
3. Else if BF5<1, return 0
4. Else return (BH5-$AT5)*BF5 only if __all_3__ previous conditions fail.

I assume that is really what you want.

In contrast, your logic returns (BH5-$AT5)*BF5 if __any_1_of_3__ previous
conditions fails.

IF "ES" or "TPG" which is only part of a part number
is in cell H then 0 otherwaise same formula.
[....] I do not know how to do step 3.

Assuming I interpreted your intent correctly:

=IF(OR(H5={"VEN-NUM","STARTUP"},E5={106547,112565,133256},BF5<1),0,
IF(OR(ISNUMBER(SEARCH({"ES","TPG"},E5))),0,(BH5-$AT5)*BF5))

Technically, we could avoid the nested IF by including
ISNUMBER(SEARCH({"ES","TPG"},E5)) as another parameter of the first OR()
function.

But the nested IF is more efficient since the any of the other conditions
being true would preclude the need to do the searches.

 

[ljochmann]

If any of the statements are FALSE then do (BH5-AT5)*BF5. Does not nee
to be a combination of one or the other, just is any are false then
would like the formuala to happen. I hope that makes more sense fo
you.
Thanks for helping.
Lynnette

 

[Claus Busch]

Hi Lynette,

Am Mon, 3 Mar 2014 17:43:48 +0000 schrieb ljochmann:

If any of the statements are FALSE then do (BH5-AT5)*BF5. Does not need
to be a combination of one or the other, just is any are false then I
would like the formuala to happen.

then try:
=IF(OR(E5={106547;112565;133256},COUNT(SEARCH({"VEN-NUM";"STARTUP";"ES";"TPG"},H5)),BF5<1),0,(BH5-AT5)*BF5)


Regards
Claus B.

 

[ljochmann]

This worked. Thank you so much for your help.
I used
=IF(OR(H5={"VEN-NUM","STARTUP"},E5={106547,112565,133256},BF5<1),0 ,
IF(OR(ISNUMBER(SEARCH({"ES","TPG"},E5))),0,(BH5-$AT5)*BF5))

Again - Thank you.

;1616503']Lynnette wrote:-

I am thinking there is an easier way- [....]-
=IF(H5="VEN-NUM",0,IF(H5="STARTUP",0,IF(E5=106547,0,
IF(E5=112565,0,IF(E5=133256,0,IF(BF5<1,0,(BH5-$AT5)*BF5))))))-

For what you have so far, you could write instead:

=IF(OR(H5={"VEN-NUM","STARTUP"},E5={106547,112565,133256},BF5<1),
0,(BH5-$AT5)*BF5)

Be careful to distinguish curly braces after H5= and E5= v. parenthesis

That might be difficult to see in some fonts. It is best t
copy-and-paste
the alternative above.

However....


Lynnette wrote:-

1. IF E5 = 106547 or 112565 or 133256 then 0...
If False then (BH5-AT5)*BF5
2. IF H5 = "VEN-NUM" or "STARTUP" then 0...
If False then (BH5-AT5)*BF5-

You original expression does not implement that logic. Instead, your
original expression implements the following logic:

1. If H5 = "VEN-NUM" or "STARTUP", return 0
2. Else if E5 = 106547 or 112565 or 133256, return 0
3. Else if BF5<1, return 0
4. Else return (BH5-$AT5)*BF5 only if __all_3__ previous condition
fail.

I assume that is really what you want.

In contrast, your logic returns (BH5-$AT5)*BF5 if __any_1_of_3_
previous
conditions fails.


Lynnette wrote:-

IF "ES" or "TPG" which is only part of a part number
is in cell H then 0 otherwaise same formula.
[....] I do not know how to do step 3.-

Assuming I interpreted your intent correctly:

=IF(OR(H5={"VEN-NUM","STARTUP"},E5={106547,112565,133256},BF5<1),0,
IF(OR(ISNUMBER(SEARCH({"ES","TPG"},E5))),0,(BH5-$AT5)*BF5))

Technically, we could avoid the nested IF by including
ISNUMBER(SEARCH({"ES","TPG"},E5)) as another parameter of the first OR(

function.

But the nested IF is more efficient since the any of the othe
conditions
being true would preclude the need to do the searches

 

[ljochmann]

Looks like this formula is working
=IF(OR(E5={106547;112565;133256},COUNT(SEARCH({"V
N-NUM";"STARTUP";"ES";"TPG"},H5)),BF5<1),0,(BH5-AT5)*BF5)


Thank you for your help.
Lynnette