Simplify formula please

I

Iriemon

I am getting confused trying to simplify these two formulas into one. I
currently have them in two separate cells and would like to get them into a
single cell.

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.5,$L2>=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.7,$L2>=-1)),7,"")


As you can see, the only difference is in the L2 values and the value to
return. I'm getting lost!

Thanks

Irie
 
E

Eduardo

Hi,
try

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.5,$L2>=-0.7)),6,IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.7,$L2>=-1)),7,""))
 
N

NBVC

Iriemon;452920 said:
I am getting confused trying to simplify these two formulas into one. I
currently have them in two separate cells and would like to get the
into a
single cell.

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.5,$L2>=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.7,$L2>=-1)),7,"")


As you can see, the only difference is in the L2 values and the valu
to
return. I'm getting lost!

Thanks

Irie


Try:

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0),IF(AND($L2<-0.5,$L2>=-0.7),6,IF(AND($L2<-0.7,$L2>=-1),7,"")),""

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com
 
J

Jacob Skaria

Try

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0),IF(AND($L2<-0.5,$L2>=-0.7),6,IF(AND($L2<-0.7,$L2>=-1),7,""),"")

If this post helps click Yes
 
D

David Biddulph

To start with, you don't need the second AND in each equation, as you're
already within an AND function, hence:
=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,$L2<-0.5,$L2>=-0.7),6,"") =IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,$L2<-0.7,$L2>=-1),7,"")You could do things the long way & just say:=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,$L2<-0.5,$L2>=-0.7),6,IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,$L2<-0.7,$L2>=-1),7,""))Another option is:=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0),IF($L2>=-0.5,"",IF($L2>=-0.7,6,IF($L2>=-1,7,""))),"")--David Biddulph"Iriemon" <[email protected]> wrote in messageam getting confused trying to simplify these two formulas into one. I> currently have them in two separate cells and would like to get them intoa> single cell.>>=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.5,$L2>=-0.7)),6,"")>>=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E2>0,AND($L2<-0.7,$L2>=-1)),7,"")>>> As you can see, the only difference is in the L2 values and the value to> return. I'm getting lost!>> Thanks>> Irie
 
I

Iriemon

Thanks all for the help!

Eduardo: I appreciate the formula help, yours is very easy to "read" and
makes following the logic simple. I used yours to validate the others.

NVBC: Your formula is shorter and is also easy to read, I'm actually
currently using this one.

David: Yours is the shortest and I will probably eventually use yours, but
for now, trying to follow the logic is more difficult. Once I get it all
validated, I'll switch them to your version. ( I have several more conditions
I have to test for)


THANKS ALL !

Irie!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top