Multi functions

S

SR

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

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
 
K

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
 
S

SR

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.
 
S

SR

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.
 
K

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
 
S

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
[email protected]
Replace@mailinator with @tiscali.co.uk
 
Top