Formula with conditions

[seeblue]

Excel 2010- in cell Y2 I need a formula that if cell x2 is blank leave blank, if J2 ="crude" and X2 if greater than the ABS of 0.2 then cell equals Y, otherwise if X2 is greater than the abs of 0.5 then cell is equal to Y otherwise cell value is equal to N

 

[Tom Urtis]

"X2 if greater than the ABS of 0.2 then cell equals Y, otherwise if X2 is greater than the abs of 0.5 then cell is equal to Y"

Why do you have two separate conditions that evaluate to the same result?

 

[seeblue]

I am looking for intransit loss -- for crude moves(indentifed in column J) we have a 0.2% tolerance, everything else is a difference of .5%. there are instances where I don't have both of those numbers so will need the cell to be blank. Hope this makes sense.

 

[Aladin Akyurek]

Excel 2010- in cell Y2 I need a formula that if cell x2 is blank leave blank, if J2 ="crude" and X2 if greater than the ABS of 0.2 then cell equals Y, otherwise if X2 is greater than the abs of 0.5 then cell is equal to Y otherwise cell value is equal to N

I am looking for intransit loss -- for crude moves(indentifed in column J) we have a 0.2% tolerance, everything else is a difference of .5%. there are instances where I don't have both of those numbers so will need the cell to be blank. Hope this makes sense.

Still underspecified... You seem saying something like:

1. X2 = blank ---> blank;

2. J2 = crude and Abs(X2) > 0.2 ---> Y;

3. Abs(X2) > 0.5 ---> Y;

4. Otherwise N.

If the specs in 2 and 3 are indeed what you intend to have, then what we have in J2 becomes irrelevant for an Y outome, that is:

=IF(X2="",
    "",
    IF(ABS(X2) > 0.2, 
      "Y", 
      "N"))

would suffice.

 

[seeblue]

ADVERTISEMENT

Appreciate the feed back. Actually column J is the key to the OOT flag. If column J is equal to crude and column X is >0.2 then Y2 would contain a "Y","N", otherwise the the value has to be greater than 0.5

The tolerance band is stated for crude oils anything greater than 0.2% is OOT all other products anything over 0.5% is considered OOT. The only way to apply the tolerance range would be to look in column J to see if it contains the word "crude" or not.

 

[Aladin Akyurek]

Appreciate the feed back. Actually column J is the key to the OOT flag. If column J is equal to crude and column X is >0.2 then Y2 would contain a "Y","N", otherwise the the value has to be greater than 0.5

The tolerance band is stated for crude oils anything greater than 0.2% is OOT all other products anything over 0.5% is considered OOT. The only way to apply the tolerance range would be to look in column J to see if it contains the word "crude" or not.

Still unlear I'm afraid. Could you perhaps revise the list:

1. X2 = blank ---> blank;

2. J2 = crude and Abs(X2) > 0.2 ---> Y;

3. Abs(X2) > 0.5 ---> Y;

4. Otherwise N.

or provide a similar one?

 

[Dryver14]

ADVERTISEMENT

Try This

=IF(X2="","",IF(AND(J2="Crude",X2>0.2),"Y",IF(X2>0.5,"Y","N")))

 

[seeblue]

I beleive that formula will work, I just need to add the ABS to the 0.2 amd 0.5 values

If(x2="","",if(J2="Crude",if(abs(x2)>=0.2,"Y",N"),if(abs(x2>=0.5,"Y","N")))


Thanks to everyone for their help!!

 

[Aladin Akyurek]

I beleive that formula will work, I just need to add the ABS to the 0.2 amd 0.5 values

If(x2="","",if(J2="Crude",if(abs(x2)>=0.2,"Y",N"),if(abs(x2>=0.5,"Y","N")))


Thanks to everyone for their help!!

That is:

=IF(X2="","",IF(AND(J2="Crude",ABS(X2)>0.2),"Y",IF(ABS(X2)>0.5,"Y","N")))