If then formula help

[Aimee S.]

Hi Folks.

What would a formula be that does the following where there are two columns, A and B, and conditions are only one of those two columns at any given time will have a value, while the other is blank.

A B
__3___ ______
______ __1___
__5___ ______

I want column C to have a formula that says something to the effect of, IF there is a value in column A but none in column B, pull value in Column A, but also the inverse (if there is a value in Column B but none in Column A, pull the only available value from Column B).

 

[VoG]

Hi. Try

=IF(A1="",B1,A1)

 

[Aimee S.]

Awesome! Works.

Is there a way to take it one step further and say all this in one statement?:

IFF D2 contains exactly "ISP & OSP - PRC"
and K2="",L2,K2

Lastly, what formula would give me the following:

If either column H and/or column I have value of zero (meaning either H or I has value of zero or both of them do), then "Eng Not Complete", otherwise "ISP/OSP Complete"

 

[VoG]

Try

=IF(AND(D2="ISP & OSP - PRC",K2=""),L2,K2)

=IF(OR(H2=0,I2=0),"Eng Not Complete","ISP/OSP Complete")

 

[Aimee S.]

Awesome. Using that logic I also tried the following but it wasn't working:

If Column J has =IF(OR(H2=0,I2=0),"0","1") and produces a 0
I have Column M off to the side where =IF(AND(J2="1",K2=""),L2,K2)
and even though K2 is not blank, but J2 does not = "1"
the column M formula is still pulling the value from K2 as if it were ignoring the (AND(J2="1" portion of the formula.

 

[VoG]

Try removing the quotes

=IF(AND(J2=1,K2=""),L2,K2)

 

[Aimee S.]

Good thought. But alas it didn't change anything.

 

[VoG]

Try

=IF(AND(J2=1,K2=""),L2,K2)

 

[Aimee S.]

I tried on a whim to make the formula this instead by inversing the K and L:

=IF(AND(J2=1,L2=""),K2,L2)

And it worked! Bizarre...

 

[Aimee S.]

Thx for all your help