IF AND statements

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

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
 
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"))
 
=IF(OR(G2="a",G2="a3",G2="m",G2="m3",G2="ftm"),IF(H2<>"",IF(K2<>"",0.5,1),IF(K2<>"",1,"H2
and K2 both blank so answer undefined")),"G2 not one of specified values so
answer undefined")
 
An alternative to David's formula:
=IF(AND(OR(G2={"a","a3","m","m3","ftm"})+0,OR(H2<>"",K2<>"")),1/((H2<>"")+(K2<>"")),"unmatched")

The other 2 formulas offered so far (S & MH), fail in certain areas).
 
Do you need the +0 ?
--
David Biddulph

John C said:
An alternative to David's formula:
=IF(AND(OR(G2={"a","a3","m","m3","ftm"})+0,OR(H2<>"",K2<>"")),1/((H2<>"")+(K2<>"")),"unmatched")

The other 2 formulas offered so far (S & MH), fail in certain areas).
Click to expand...
 
Nope. Early version, I thought I did, and just never got rid of it. Kind of
interesting, checkmarks go to the other 2 respondents, when the formulas
don't match OP's request, lol.
 
Back
Top