View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sheeloo[_3_] Sheeloo[_3_] is offline
external usenet poster
 
Posts: 1,805
Default IF AND statements

Try
=IF(OR(G2="a",G2="a3",G2="m",G2="m3",G2="ftm"),IF( AND(H2="",K2=""),0.5,1),"No match")

or the longer version
=IF(AND(OR(G2="a",G2="a3",G2="m",G2="m3",G2="ftm") ,H2="",K2=""),0.5,IF(AND(OR(G2="a",G2="a3",G2="m", G2="m3",G2="ftm"),OR(H2<"",K2<"")),1,"No match"))

"bob" wrote:

I'm seeking a formula for the following problem:

IF G2 = "a" or "a3" or "m" or "m3" or "ftm"
AND H2 and K2 are populated with any value (that is, they are not blank
cells),
then the formula should return a value of 0.5

BUT if the G2 equals any of the above values
AND H2 is populated with any value
AND K2 is blank (has nothing in the cell),
then the formula should return a value of 1

BUT if the G2 equals any of the above values
AND K2 is populated with any value
AND H2 is blank (has nothing in the cell),
then the formula should return a value of 1

Can anyone help?

many thanks,
Bob