Multi functions

Going nuts trying to make this equation fit all possible situations - I'd
like an equation to put in G that would look at mutliple IFs/ANDs:

IF B="in house" and C=Jack then E x .05
IF B="in house" and C=Jill then E x .05
IF B="in house" and C=(no name) then E x .06
IF B=anything other than "in house" and C=Jack then E x .01
IF B= anything other than "in house" and C=Jill then E x .01
IF B=anything other than "in house" and C=no name then E x .02

Is this possible to do in one equation? All suggestions welcome. Thank you.

=IF(B1="in
house",IF(OR(C1="jack",C1="jill"),0.05,ISBLANK(C1)*0.06),IF(OR(C1="jack",C1="jill"),0.01,ISBLANK(C1)*0.02))
will give you either 0.05,0.06,0.01 or 0.02 as output based on the
combinatiion of data on cells B1 and c1



"SR" <SR@discussions.microsoft.com> wrote in message
news:1AAFB758-F86C-49BA-AFDF-7B364398E962@microsoft.com...
> Going nuts trying to make this equation fit all possible situations - I'd
> like an equation to put in G that would look at mutliple IFs/ANDs:
>
> IF B="in house" and C=Jack then E x .05
> IF B="in house" and C=Jill then E x .05
> IF B="in house" and C=(no name) then E x .06
> IF B=anything other than "in house" and C=Jack then E x .01
> IF B= anything other than "in house" and C=Jill then E x .01
> IF B=anything other than "in house" and C=no name then E x .02
>
> Is this possible to do in one equation? All suggestions welcome. Thank
> you.

You could try something like this:

=IF(AND(B1="in house",C1="Jack"), E1*0.05,IF(AND(B1="in
house",C1="Jill"),E1*0.05,IF(AND(B1="in house",C1=""),E1*0.06,IF(AND(B1<>"in
house",C1="Jack"),E1*0.01,IF(AND(B1<>"in
house",C1="Jill"),E1*0.01,IF(AND(B1<>"in house",C1=""),E1*0.02,""))))))

Regards,
KL

"SR" <SR@discussions.microsoft.com> wrote in message
news:1AAFB758-F86C-49BA-AFDF-7B364398E962@microsoft.com...
> Going nuts trying to make this equation fit all possible situations - I'd
> like an equation to put in G that would look at mutliple IFs/ANDs:
>
> IF B="in house" and C=Jack then E x .05
> IF B="in house" and C=Jill then E x .05
> IF B="in house" and C=(no name) then E x .06
> IF B=anything other than "in house" and C=Jack then E x .01
> IF B= anything other than "in house" and C=Jill then E x .01
> IF B=anything other than "in house" and C=no name then E x .02
>
> Is this possible to do in one equation? All suggestions welcome. Thank
> you.

I apologize for not thanking you earlier for your quick response. I just
found this discussion group of excel's and I am so impressed by people's
willingness to help others. So, I thank you for your time. Unfortunately, I
could not get this to work. I'm sure it is my lack of experience with
compound and complicated functions. I will continue to study your suggestion
and learn what I can from it.

"N Harkawat" wrote:

> =IF(B1="in
> house",IF(OR(C1="jack",C1="jill"),0.05,ISBLANK(C1)*0.06),IF(OR(C1="jack",C1="jill"),0.01,ISBLANK(C1)*0.02))
> will give you either 0.05,0.06,0.01 or 0.02 as output based on the
> combinatiion of data on cells B1 and c1
>
>
>
> "SR" <SR@discussions.microsoft.com> wrote in message
> news:1AAFB758-F86C-49BA-AFDF-7B364398E962@microsoft.com...
> > Going nuts trying to make this equation fit all possible situations - I'd
> > like an equation to put in G that would look at mutliple IFs/ANDs:
> >
> > IF B="in house" and C=Jack then E x .05
> > IF B="in house" and C=Jill then E x .05
> > IF B="in house" and C=(no name) then E x .06
> > IF B=anything other than "in house" and C=Jack then E x .01
> > IF B= anything other than "in house" and C=Jill then E x .01
> > IF B=anything other than "in house" and C=no name then E x .02
> >
> > Is this possible to do in one equation? All suggestions welcome. Thank
> > you.
>
>
>

I apologize for not thanking you earlier for your quick response. As I just
explained to "N Harkawat" the other person who responded to my plea, I am so
impressed by people's willingness to help others. So, I thank you for your
time. Unfortunately, I could not get this to work. I'm sure it is my lack
of experience with compound and complicated functions. I will continue to
study your suggestion and learn what I can from it. Again, I thank you very
much.

"KL" wrote:

> You could try something like this:
>
> =IF(AND(B1="in house",C1="Jack"), E1*0.05,IF(AND(B1="in
> house",C1="Jill"),E1*0.05,IF(AND(B1="in house",C1=""),E1*0.06,IF(AND(B1<>"in
> house",C1="Jack"),E1*0.01,IF(AND(B1<>"in
> house",C1="Jill"),E1*0.01,IF(AND(B1<>"in house",C1=""),E1*0.02,""))))))
>
> Regards,
> KL
>
> "SR" <SR@discussions.microsoft.com> wrote in message
> news:1AAFB758-F86C-49BA-AFDF-7B364398E962@microsoft.com...
> > Going nuts trying to make this equation fit all possible situations - I'd
> > like an equation to put in G that would look at mutliple IFs/ANDs:
> >
> > IF B="in house" and C=Jack then E x .05
> > IF B="in house" and C=Jill then E x .05
> > IF B="in house" and C=(no name) then E x .06
> > IF B=anything other than "in house" and C=Jack then E x .01
> > IF B= anything other than "in house" and C=Jill then E x .01
> > IF B=anything other than "in house" and C=no name then E x .02
> >
> > Is this possible to do in one equation? All suggestions welcome. Thank
> > you.
>
>
>

Hi SR,

So do you want to leave it there, or do you want to make it work? :-)
If you explain what you mean by "I could not get this to work" we may try
and resolve it.

Regards,
KL


"SR" <SR@discussions.microsoft.com> wrote in message
news:2FFA73EC-DBF7-435E-8989-CFEA645D4559@microsoft.com...
>I apologize for not thanking you earlier for your quick response. As I
>just
> explained to "N Harkawat" the other person who responded to my plea, I am
> so
> impressed by people's willingness to help others. So, I thank you for
> your
> time. Unfortunately, I could not get this to work. I'm sure it is my
> lack
> of experience with compound and complicated functions. I will continue to
> study your suggestion and learn what I can from it. Again, I thank you
> very
> much.
>
> "KL" wrote:
>
>> You could try something like this:
>>
>> =IF(AND(B1="in house",C1="Jack"), E1*0.05,IF(AND(B1="in
>> house",C1="Jill"),E1*0.05,IF(AND(B1="in
>> house",C1=""),E1*0.06,IF(AND(B1<>"in
>> house",C1="Jack"),E1*0.01,IF(AND(B1<>"in
>> house",C1="Jill"),E1*0.01,IF(AND(B1<>"in house",C1=""),E1*0.02,""))))))
>>
>> Regards,
>> KL
>>
>> "SR" <SR@discussions.microsoft.com> wrote in message
>> news:1AAFB758-F86C-49BA-AFDF-7B364398E962@microsoft.com...
>> > Going nuts trying to make this equation fit all possible situations -
>> > I'd
>> > like an equation to put in G that would look at mutliple IFs/ANDs:
>> >
>> > IF B="in house" and C=Jack then E x .05
>> > IF B="in house" and C=Jill then E x .05
>> > IF B="in house" and C=(no name) then E x .06
>> > IF B=anything other than "in house" and C=Jack then E x .01
>> > IF B= anything other than "in house" and C=Jill then E x .01
>> > IF B=anything other than "in house" and C=no name then E x .02
>> >
>> > Is this possible to do in one equation? All suggestions welcome. Thank
>> > you.
>>
>>
>>

A bit more complicated so if you had trouble with KL's formula perhaps you
would be better letting him/her talk you through it but if can be shortened
to:

=IF(AND(B1="In
House",OR(C1={"Jack","Jill",""})),E1*(0.05+(C1="")/100),IF(AND(B1<>"In
House",OR(C1={"Jack","Jill",""})),E1*(0.01+(C1="")/100),"Wrong Data"))

The "Wrong Data" was added in case of typo's in the raw data etc.


--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"SR" <SR@discussions.microsoft.com> wrote in message
news:2FFA73EC-DBF7-435E-8989-CFEA645D4559@microsoft.com...
> I apologize for not thanking you earlier for your quick response. As I
just
> explained to "N Harkawat" the other person who responded to my plea, I am
so
> impressed by people's willingness to help others. So, I thank you for
your
> time. Unfortunately, I could not get this to work. I'm sure it is my
lack
> of experience with compound and complicated functions. I will continue to
> study your suggestion and learn what I can from it. Again, I thank you
very
> much.
>
> "KL" wrote:
>
> > You could try something like this:
> >
> > =IF(AND(B1="in house",C1="Jack"), E1*0.05,IF(AND(B1="in
> > house",C1="Jill"),E1*0.05,IF(AND(B1="in
house",C1=""),E1*0.06,IF(AND(B1<>"in
> > house",C1="Jack"),E1*0.01,IF(AND(B1<>"in
> > house",C1="Jill"),E1*0.01,IF(AND(B1<>"in house",C1=""),E1*0.02,""))))))
> >
> > Regards,
> > KL
> >
> > "SR" <SR@discussions.microsoft.com> wrote in message
> > news:1AAFB758-F86C-49BA-AFDF-7B364398E962@microsoft.com...
> > > Going nuts trying to make this equation fit all possible situations -
I'd
> > > like an equation to put in G that would look at mutliple IFs/ANDs:
> > >
> > > IF B="in house" and C=Jack then E x .05
> > > IF B="in house" and C=Jill then E x .05
> > > IF B="in house" and C=(no name) then E x .06
> > > IF B=anything other than "in house" and C=Jack then E x .01
> > > IF B= anything other than "in house" and C=Jill then E x .01
> > > IF B=anything other than "in house" and C=no name then E x .02
> > >
> > > Is this possible to do in one equation? All suggestions welcome.
Thank
> > > you.
> >
> >
> >