Involved formula

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I am trying to create a formula based on a number of
conditions. I have tried "AND" and "OR" statements but
can't seem to make things work. Logically, here is what I
want to do:

if S8="PROFIT", "INCLUDE","EXCLUDE" else
if S8="LOSS" and V8="NO" and W8="NO", "INCLUDE" else
if S8="LOSS" and V8="NO" and W8="YES", "INCLUDE" else
if S8="LOSS" and V8="NO" and W8="YES" and
X8="YES", "EXCLUDE" else
if S8="LOSS" and V8="YES"and X8="NO", "INCLUDE" else
if S8="LOSS" and V8="YES"and X8="YES", "EXCLUDE"

Can anybody help, please?

Oh, this is Excel 2002 SP2

Thanks,

Bruce
 
I'm a little confuse by the first line.

' if S8="PROFIT", "INCLUDE","EXCLUDE" else '

It looks like you want to have the string "include" if S
= "Profit" and if it's equal to "Loss" then you have some
further checking to do.

Try this: It looks for the "exclude" condtions and assumes
the rest are "includes".

By the way, substitute "YES" for "Y" and "NO" for "N"


=if(or(and(v8="Y",x8="Y"),and
(v8="n",w8="Y",x8="Y")),"exclude","include")


Good Luck
 
Sorry. The "INCLUDE" and "EXCLUDE" are actual text that we want to have
written to the cell. The same goes for "YES" and "NO"

Bruce
 
If the only two values that S8 can have are PROFIT and LOSS, then

=IF(S8="PROFIT", "INCLUDE", IF(V8="YES", IF(X8="YES", "EXCLUDE",
INCLUDE"), IF(AND(W8="YES", X8="YES"), "EXCLUDE", "INCLUDE")))
 
I am trying to create a formula based on a number of
conditions. I have tried "AND" and "OR" statements but
can't seem to make things work. Logically, here is what I
want to do:
Click to expand...

Have you looked at the online help for these functions ?

EG: IF Function syntax is: =IF(condition, Dothis, Elsedothis)
if S8="PROFIT", "INCLUDE","EXCLUDE" else
Click to expand...

So, what are you trying to do here ?

IF (S8="PROFIT", "INCLUDE", "EXCLUDE") will put either INCLUDE or EXCLUDE
into the current cell depending on if the the value of cell S8 = PROFIT -
why have you go an "else" after it ?

Are you trying to nest an IF statement ?

If you are, try this:

IF(S8="PROFIT", IF(S9="ABCD", "Right", "Wrong"), "Exclude")

You can keep nesting but the longer the string condition the harder it is to
understand.

The AND & OR functions are similar

IF(AND(S8="PROFIT",S9="BIG"), "INCLUDE", "EXCLUDE") - puts the word INCLUDE
into the current cell if S8=PROFIT and S9 = BIG otherwise it puts the word
EXCLUDE into the current cell

IF(OR(S8="PROFIT",S9="BIG"), "INCLUDE", "EXCLUDE") - puts the word INCLUDE
into the current cell if S8=PROFIT or S9 = BIG otherwise it puts the word
EXCLUDE into the current cell

Is this clearer ?

HTH

Chris
 
Brilliant! Thanks so much.

Bruce


J.E. McGimpsey said:
If the only two values that S8 can have are PROFIT and LOSS, then

=IF(S8="PROFIT", "INCLUDE", IF(V8="YES", IF(X8="YES", "EXCLUDE",
INCLUDE"), IF(AND(W8="YES", X8="YES"), "EXCLUDE", "INCLUDE")))
Click to expand...
 
Back
Top