Logical Functions

[Eden397]

Hi,
If I have a certain cell with text in it that I am hard coding as Female or
Male, and I want the next cell down to automatically state the opposite sex
than that cell, what logical function would I use?

Thanks,

 

[PCLIVE]

In A2:

=IF(A1="Male","Female",IF(A1="Female","Male",""))

HTH,
Paul

 

[bj]

if you place Male or Female in C1
in C2 enter
=if(C1="Male",Female","Male")
and copy down as far as you need

 

[PCLIVE]

bj,

Your formula is missing a Quote before Female. Anyway, to keep the cell
from populating when nothing is in the cell above, an additional statement
is needed. Where A1 is the cell to enter Male or Female:

=IF(A1="","",IF(A1="Female","Male",""))

Regards,
Paul

 

[Eden397]

In A2:

=IF(A1="Male","Female",IF(A1="Female","Male",""))

HTH,
Paul




Thank you very much. This actually helped me clear up nesting issues.

 

[Erny]

Example if certain cell is A1:
In A2, formula is:
=IF(A1="Male";"Female";IF(A1="Female";"Male";""))
(If A1 is not "Male" or "Female" then the cell will not display anything
with the above formula.)

Kind regards,
Erny

 

[PCLIVE]

Did you try your formula? You've written it using semi-colons ( instead
of commas (,).

Anyway, the formula is cleaner if you test for blank first.
=IF(A1="","",IF(A1="Female","Male","Female"))

Regards,
Paul

 

[Eden397]

Thanks, I got it to work and it cleared up my issues. But it lead me to one
other question....Here is my formula
=IF(Combo_4_000664!C11="All","1",IF(Combo_4_000664!C11="quota","0",IF(Combo_4_000664!C11="222","1")))

but I am getting a "FALSE" when I enter 222 in my linked spreadsheet. I
believe it's because I am already using a "1" for my All case, so does anyone
know how to rewrite this to clean it up. I really appreciate this help,
everyone.

 

[PCLIVE]

Remove the quotes from "222".

=IF(Combo_4_000664!C11="All","1",IF(Combo_4_000664!C11="quota","0",IF(Combo_4_000664!C11=222,"1")))

Will C11 ever be Blank? If so, what do you want displayed? If you want it
to be blank, then:
=IF(Combo_4_000664!C11="All","1",IF(Combo_4_000664!C11="quota","0",IF(Combo_4_000664!C11=222,"1","")))

Regards,
Paul

 

[Eden397]

Thanks, that worked. Are numbers not suppose to have quotations? Also, I
would like to have the cell blank if nothing is entered.

 

[PCLIVE]

If C11 was formatted as Text, then it would work. However, if it is in the
default format of General, then 222 will be seen as a number and "222" with
quotes will not match since the quotation markes make it a text string. Try
this simpler formula below. The "All" and 222 have been combined into an OR
statement since the result will be the same.

=IF(OR(Combo_4_000664!C11="All",(Combo_4_000664!C11=222)),"1",IF(Combo_4_000664!C11="quota","0",""))

HTH,
Paul