Simplify formula please
 

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"),$E20 ,AND($L2<-0.5,$L2=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E20 ,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

Simplify formula please
 

Hi,
try

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

"Iriemon" wrote:

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"),$E20 ,AND($L2<-0.5,$L2=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E20 ,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

Simplify formula please
 

Iriemon;452920 Wrote:
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"),$E20 ,AND($L2<-0.5,$L2=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E20 ,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


Try:

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


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125361

Simplify formula please
 

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Iriemon" wrote:

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"),$E20 ,AND($L2<-0.5,$L2=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E20 ,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

Simplify formula please
 

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"),$E20 ,$L2<-0.5,$L2=-0.7),6,"") =IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E20 ,$L2<-0.7,$L2=-1),7,"")You could do things the long way & just say:=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$ E20,$L2<-0.5,$L2=-0.7),6,IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00") ,$E20,$L2<-0.7,$L2=-1),7,""))Another option is:=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E 20),IF($L2=-0.5,"",IF($L2=-0.7,6,IF($L2=-1,7,""))),"")--David Biddulph"Iriemon" wrote in ...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 intoa single cell.=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00" ),$E20,AND($L2<-0.5,$L2=-0.7)),6,"")=IF(AND(OR($B2="FF00",$B2="BE00",$B2= "LU00"),$E20,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

Simplify formula please
 

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!

"Iriemon" wrote:

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"),$E20 ,AND($L2<-0.5,$L2=-0.7)),6,"")

=IF(AND(OR($B2="FF00",$B2="BE00",$B2="LU00"),$E20 ,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