Formula with conditions

seeblue

Board Regular
Joined
Jan 23, 2005
Messages
178
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
"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

Board Regular
Joined
Jan 23, 2005
Messages
178
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:
Rich (BB code):
=IF(X2="",
    "",
    IF(ABS(X2) > 0.2, 
      "Y", 
      "N"))
would suffice.
 

seeblue

Board Regular
Joined
Jan 23, 2005
Messages
178

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

Try This

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

seeblue

Board Regular
Joined
Jan 23, 2005
Messages
178
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!!
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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")))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,753
Members
414,335
Latest member
cleverExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top