Multi functions

[Guest]

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.

 

[N Harkawat]

=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

 

[KL]

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

 

[Guest]

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.

 

[Guest]

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]

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

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk