If function help

Hi

Try this:
=IF(BY2="N",IF(M2<4,"None","Married"),IF(AND(BY2="Y",M2>=4),"Family",IF(AND(BY2="C",OR(M2=2,M2=3)),"Single","")))

--
Andy.


"SCOOBYDOO" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

=IF(AND(OR(M2=1,M2=2,M2=3),B2="N"),"None",IF(AND(OR(M2=2,M2=3),B2="C"),"Sing
le",IF(M2>=4,IF(B2="N","Married","Family"))))

--
HTH

Bob Phillips

"SCOOBYDOO" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

Try
=if(M1>=4,if(BY1="N","Married","Family"),if(BY1="N","None",If(M1=1,"????",""Single")
I assumed you meant if grade = 4 and BY = C
I don't know what you want when grade is 1 and BY is C

"SCOOBYDOO" wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

One way (assuming your "Y" in the last line should have been a "C"):

=CHOOSE(IF(BY1="N",2*(M1>=4),(M1>1)+2*(M1>3)+1),"None","Single","Married"
,"Family")

In article <[email protected]>,
SCOOBYDOO <[email protected]> wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

I think this will answer your question:

=IF(AND(M2<=3, BY2="n"), "none", IF( AND( OR(M2=2, M2=3), BY2="c"), "single", IF( AND( M2=4, BY2="n"), "Married", IF( AND( M2=4, BY2="c"), "Family", “unknown”))))

Comments:
1. Put this formula in the desired column and copy down. I assumed the data started in row 2, so adjust as needed.
2. You didn't specify the result of GRADE = 1 and BY = C, so I put in "unknown". Change as you need to.
3. I assumed "family" was the answer if GRADE = 4 and BY = "C".

Hope this helps.



"SCOOBYDOO" <[email protected]> wrote in message news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

=IF(M1=4,IF(BY1="Y","Family","Married"),IF(BY1="C","Single","None"))

Assumes only your given conditions can exist. For example you would never
have a 1 in column M and a "C" in Column BY

"SCOOBYDOO" wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

=IF(M1=4,IF(BY1="Y","Family","Married"),IF(BY1="C","Single","None"))

Assumes only your given conditions can exist. For example you would never
have a 1 in column M and a "C" in Column BY

"SCOOBYDOO" wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

I think this will answer your question:

=IF(AND(M2<=3, BY2="n"), "none", IF( AND( OR(M2=2, M2=3), BY2="c"), "single", IF( AND( M2=4, BY2="n"), "Married", IF( AND( M2=4, BY2="c"), "Family", “unknown”))))

Comments:
1. Put this formula in the desired column and copy down. I assumed the data started in row 2, so adjust as needed.
2. You didn't specify the result of GRADE = 1 and BY = C, so I put in "unknown". Change as you need to.
3. I assumed "family" was the answer if GRADE = 4 and BY = "C".

Hope this helps.



"SCOOBYDOO" <[email protected]> wrote in message news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

One way (assuming your "Y" in the last line should have been a "C"):

=CHOOSE(IF(BY1="N",2*(M1>=4),(M1>1)+2*(M1>3)+1),"None","Single","Married"
,"Family")

In article <[email protected]>,
SCOOBYDOO <[email protected]> wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

Try
=if(M1>=4,if(BY1="N","Married","Family"),if(BY1="N","None",If(M1=1,"????",""Single")
I assumed you meant if grade = 4 and BY = C
I don't know what you want when grade is 1 and BY is C

"SCOOBYDOO" wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

=IF(AND(OR(M2=1,M2=2,M2=3),B2="N"),"None",IF(AND(OR(M2=2,M2=3),B2="C"),"Sing
le",IF(M2>=4,IF(B2="N","Married","Family"))))

--
HTH

Bob Phillips

"SCOOBYDOO" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

Hi

Try this:
=IF(BY2="N",IF(M2<4,"None","Married"),IF(AND(BY2="Y",M2>=4),"Family",IF(AND(BY2="C",OR(M2=2,M2=3)),"Single","")))

--
Andy.


"SCOOBYDOO" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

I think this will answer your question:

=IF(AND(M2<=3, BY2="n"), "none", IF( AND( OR(M2=2, M2=3), BY2="c"), "single", IF( AND( M2=4, BY2="n"), "Married", IF( AND( M2=4, BY2="c"), "Family", “unknown”))))

Comments:
1. Put this formula in the desired column and copy down. I assumed the data started in row 2, so adjust as needed.
2. You didn't specify the result of GRADE = 1 and BY = C, so I put in "unknown". Change as you need to.
3. I assumed "family" was the answer if GRADE = 4 and BY = "C".

Hope this helps.



"SCOOBYDOO" <[email protected]> wrote in message news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

=IF(M1=4,IF(BY1="Y","Family","Married"),IF(BY1="C","Single","None"))

Assumes only your given conditions can exist. For example you would never
have a 1 in column M and a "C" in Column BY

"SCOOBYDOO" wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

Hi, I need help with the following please:

Col M = Grade (values 1 - 4) and column BY is populated with either a C or
an N. I need a formula to work out entitelement based on the following:
If Grade = 1, 2 or 3 and BY = N then the answer is None
If Grade = 2 or 3 and BY = C then the answer is Single
If Grade = 4 or above and BY = N then the answer is Married
If Grade = 4 or above and BY = Y then the answer is Family

Tahnks in advance ; )

One way (assuming your "Y" in the last line should have been a "C"):

=CHOOSE(IF(BY1="N",2*(M1>=4),(M1>1)+2*(M1>3)+1),"None","Single","Married"
,"Family")

In article <[email protected]>,
SCOOBYDOO <[email protected]> wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

Try
=if(M1>=4,if(BY1="N","Married","Family"),if(BY1="N","None",If(M1=1,"????",""Single")
I assumed you meant if grade = 4 and BY = C
I don't know what you want when grade is 1 and BY is C

"SCOOBYDOO" wrote:

> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

=IF(AND(OR(M2=1,M2=2,M2=3),B2="N"),"None",IF(AND(OR(M2=2,M2=3),B2="C"),"Sing
le",IF(M2>=4,IF(B2="N","Married","Family"))))

--
HTH

Bob Phillips

"SCOOBYDOO" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )

Hi

Try this:
=IF(BY2="N",IF(M2<4,"None","Married"),IF(AND(BY2="Y",M2>=4),"Family",IF(AND(BY2="C",OR(M2=2,M2=3)),"Single","")))

--
Andy.


"SCOOBYDOO" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need help with the following please:
>
> Col M = Grade (values 1 - 4) and column BY is populated with either a C or
> an N. I need a formula to work out entitelement based on the following:
> If Grade = 1, 2 or 3 and BY = N then the answer is None
> If Grade = 2 or 3 and BY = C then the answer is Single
> If Grade = 4 or above and BY = N then the answer is Married
> If Grade = 4 or above and BY = Y then the answer is Family
>
> Tahnks in advance ; )