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 false would be (BH-AT)*AF. I also would like the formula to do a secondary IF statement that if "VEN-NUM" or "STARTUP" is in Cell H then 0 otherwise the same formula and finally IF "ES" or "TPG" which is only part of a 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 I do not know how to do step 3.

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

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

Thanks
Lynnette

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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. I hope that makes more sense for you.
Thanks for helping.
Lynnette

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({"VE N-NUM";"STARTUP";"ES";"TPG"},H5)),BF5<1),0,(BH5-AT5)*BF5)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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


Thank you for your help.
Lynnette

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


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 other conditions
being true would preclude the need to do the searches.