Multiple Conditional @IF Statement Method
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! -- crwiseman ------------------------------------------------------------------------ crwiseman's Profile: http://www.excelforum.com/member.php...o&userid=32758 View this thread: http://www.excelforum.com/showthread...hreadid=558708 |
Multiple Conditional @IF Statement Method
Try something like this: =IF(AND(A1="data1",A2<"Data2"),"X","Y") Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=558708 |
Multiple Conditional @IF Statement Method
Thanks Ron, that did the trick. -- crwiseman ------------------------------------------------------------------------ crwiseman's Profile: http://www.excelforum.com/member.php...o&userid=32758 View this thread: http://www.excelforum.com/showthread...hreadid=558708 |
Multiple Conditional @IF Statement Method
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! |
Multiple Conditional @IF Statement Method
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,TARG ET)-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! |
Multiple Conditional @IF Statement Method
'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,TARG ET)-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! |
All times are GMT +1. The time now is 03:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com