Multiple Conditional @IF Statement Method

[crwiseman]

Does anyone know how to have multiple conditional @IF criteria. What I am trying to figure out is @IF(Cell#1="data1" AND Cell#2 NOT EQUAL to "Data2, Then "X", Or "Y").

I would also need to know how Excel defines NOT EQUAL to in that kind of formula.

Many thanks!

[Ron Coderre]

Try something like this:

=IF(AND(A1="data1",A2<>"Data2"),"X","Y")

Does that help?

Regards,

Ron

[crwiseman]

Thanks Ron, that did the trick.

[Wayne Knazek]

Here's one I'm having a problem with . . . too many variable conditions in a
formula.

I need all of these conditions tested in one formula.

First of all, I've worked on this for quite a while. Not just asking for a
gimme!

I just can't get a 3rd condition into my equation.

If an input number is >= one value, OR =< the value of another number . . .
AND if it's value is < a 3rd number, then do such and such.

EX: The value of an input number is .035. The nominal (target) number is
..03. There is a tolerance range, AND an inner limit as well. So my input
number is .005> than .03.

If the number is more than .01 > or .01 < .03, then it's out of tolerance.
And an "OT" would appear in a cell. That one's easy. No problemo.

Now . . . in another cell (the "Alert" column) I need to determine whether
the value uses 75% of the allowable + or - tolerance. But not more than
100%, or that would be in the OT column.

No problem with the 75% part. My formula puts a "75% Used" in the cell if I
use more than 75% of either the upper or lower end of the tolerance. Like .
.. . .03 is +/- .01. So if the input number is .009, that would be 75% or
more, AND under 100%.

If it was -.009, that's more than 75% on the minus side. So I need the "75%
Used" in the cell there, too. Because it's still under the low limit of
-.01, and therefore not OT.

If it was .0074, neither 75% nor OT would come into play.

Here's what I've tried . . .

=IF(AND(H10>=M10,H10<(N10+0.0001)),"75% + Used","")

.. . . is what I used first. But this only determined whether the data was
>75%, and <100%. problem is, I need to work in another variable.

=IF(AND(H10>=M10, <- checks to see it the data is higher than 75%.
H10<(N10+0.0001)), <- checks to see if it's less than 100%
??? I need one more condition to check that the data isn't <75% as well!!!!
"75% + Used","") <- what to do if it's 75% (+ or -) and less than 100%.

. . . Let's say that X = the difference between input# and target. Like
input is .035, target .03. Diff is +.005. And I'm comparing that difference
to a calculated value. In this case, 75% of .01 (and -.01). So y is .01 and
z is -.01 in this case.

So my formula has to "say" . . . if x>=75% of y OR =<75% of z AND less than
y then do this . . .

What a day!

[Ron Coderre]

OK...Let's see if I understand correctly:

You want to
COMPARE an INPUT to a TARGET
AND determine if the difference is within a TOLERANCE
AND if YES then
deterimine if the difference is more than 75% of TOLERANCE

If that's true.....Try this:

Sample formula:
=IF(MAX(INPUT,TARGET)-MIN(INPUT,TARGET)>TOLERANCE,"OT",IF(MAX(INPUT,TARGET)-MIN(INPUT,TARGET)>0.75*TOLERANCE,"More than 75%","OK"))

Actual Formula:
=IF(MAX(H10,N10)-MIN(H10,N10)>M10,"OT",IF(MAX(H10,N10)-MIN(H10,N10)>0.75*M10,"More than 75%","OK"))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Wayne Knazek" wrote:

> Here's one I'm having a problem with . . . too many variable conditions in a
> formula.
>
> I need all of these conditions tested in one formula.
>
> First of all, I've worked on this for quite a while. Not just asking for a
> gimme!
>
> I just can't get a 3rd condition into my equation.
>
> If an input number is >= one value, OR =< the value of another number . . .
> AND if it's value is < a 3rd number, then do such and such.
>
> EX: The value of an input number is .035. The nominal (target) number is
> .03. There is a tolerance range, AND an inner limit as well. So my input
> number is .005> than .03.
>
> If the number is more than .01 > or .01 < .03, then it's out of tolerance.
> And an "OT" would appear in a cell. That one's easy. No problemo.
>
> Now . . . in another cell (the "Alert" column) I need to determine whether
> the value uses 75% of the allowable + or - tolerance. But not more than
> 100%, or that would be in the OT column.
>
> No problem with the 75% part. My formula puts a "75% Used" in the cell if I
> use more than 75% of either the upper or lower end of the tolerance. Like .
> . . .03 is +/- .01. So if the input number is .009, that would be 75% or
> more, AND under 100%.
>
> If it was -.009, that's more than 75% on the minus side. So I need the "75%
> Used" in the cell there, too. Because it's still under the low limit of
> -.01, and therefore not OT.
>
> If it was .0074, neither 75% nor OT would come into play.
>
> Here's what I've tried . . .
>
> =IF(AND(H10>=M10,H10<(N10+0.0001)),"75% + Used","")
>
> . . . is what I used first. But this only determined whether the data was
> >75%, and <100%. problem is, I need to work in another variable.
>
> =IF(AND(H10>=M10, <- checks to see it the data is higher than 75%.
> H10<(N10+0.0001)), <- checks to see if it's less than 100%
> ??? I need one more condition to check that the data isn't <75% as well!!!!
> "75% + Used","") <- what to do if it's 75% (+ or -) and less than 100%.
>
> . . . Let's say that X = the difference between input# and target. Like
> input is .035, target .03. Diff is +.005. And I'm comparing that difference
> to a calculated value. In this case, 75% of .01 (and -.01). So y is .01 and
> z is -.01 in this case.
>
> So my formula has to "say" . . . if x>=75% of y OR =<75% of z AND less than
> y then do this . . .
>
> What a day!
>
>

[Wayne Knazek]

'Zactly what I needed! I couldn't get multiple conditions to go together! I
was leaving out the "IFs" within the formula.

Thanks!

"Ron Coderre" wrote:

> OK...Let's see if I understand correctly:
>
> You want to
> COMPARE an INPUT to a TARGET
> AND determine if the difference is within a TOLERANCE
> AND if YES then
> deterimine if the difference is more than 75% of TOLERANCE
>
> If that's true.....Try this:
>
> Sample formula:
> =IF(MAX(INPUT,TARGET)-MIN(INPUT,TARGET)>TOLERANCE,"OT",IF(MAX(INPUT,TARGET)-MIN(INPUT,TARGET)>0.75*TOLERANCE,"More than 75%","OK"))
>
> Actual Formula:
> =IF(MAX(H10,N10)-MIN(H10,N10)>M10,"OT",IF(MAX(H10,N10)-MIN(H10,N10)>0.75*M10,"More than 75%","OK"))
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Wayne Knazek" wrote:
>
> > Here's one I'm having a problem with . . . too many variable conditions in a
> > formula.
> >
> > I need all of these conditions tested in one formula.
> >
> > First of all, I've worked on this for quite a while. Not just asking for a
> > gimme!
> >
> > I just can't get a 3rd condition into my equation.
> >
> > If an input number is >= one value, OR =< the value of another number . . .
> > AND if it's value is < a 3rd number, then do such and such.
> >
> > EX: The value of an input number is .035. The nominal (target) number is
> > .03. There is a tolerance range, AND an inner limit as well. So my input
> > number is .005> than .03.
> >
> > If the number is more than .01 > or .01 < .03, then it's out of tolerance.
> > And an "OT" would appear in a cell. That one's easy. No problemo.
> >
> > Now . . . in another cell (the "Alert" column) I need to determine whether
> > the value uses 75% of the allowable + or - tolerance. But not more than
> > 100%, or that would be in the OT column.
> >
> > No problem with the 75% part. My formula puts a "75% Used" in the cell if I
> > use more than 75% of either the upper or lower end of the tolerance. Like .
> > . . .03 is +/- .01. So if the input number is .009, that would be 75% or
> > more, AND under 100%.
> >
> > If it was -.009, that's more than 75% on the minus side. So I need the "75%
> > Used" in the cell there, too. Because it's still under the low limit of
> > -.01, and therefore not OT.
> >
> > If it was .0074, neither 75% nor OT would come into play.
> >
> > Here's what I've tried . . .
> >
> > =IF(AND(H10>=M10,H10<(N10+0.0001)),"75% + Used","")
> >
> > . . . is what I used first. But this only determined whether the data was
> > >75%, and <100%. problem is, I need to work in another variable.
> >
> > =IF(AND(H10>=M10, <- checks to see it the data is higher than 75%.
> > H10<(N10+0.0001)), <- checks to see if it's less than 100%
> > ??? I need one more condition to check that the data isn't <75% as well!!!!
> > "75% + Used","") <- what to do if it's 75% (+ or -) and less than 100%.
> >
> > . . . Let's say that X = the difference between input# and target. Like
> > input is .035, target .03. Diff is +.005. And I'm comparing that difference
> > to a calculated value. In this case, 75% of .01 (and -.01). So y is .01 and
> > z is -.01 in this case.
> >
> > So my formula has to "say" . . . if x>=75% of y OR =<75% of z AND less than
> > y then do this . . .
> >
> > What a day!
> >
> >