IF(AND problem to work out commissions

A

Amanda

I am stuck using IF AND Function

The amount commission is payable on is due to certain conditions, if a
deposit has been received, if invoice has been paid in full, if full payment
received within 21 days.

I show Invoice Net amount in F16 and deposit received in G16

No commission will be paid until invoice has been paid in full and balance
must be paid within 21 days. If full payment is received but the balance is
after the 21 day period then we still need to pay commission on the deposit
rec´d (G16)

I have yes & no answers in rows - Deposit rec´d (I16), paid in full (K16),
within 21 days (N16).

What I need to do is the following:
IF I16=YES & K16=YES & NI6=YES THEN SHOW AMOUNT IN F16
IF I16=YES & K16=YES & N16=NO THEN SHOW AMOUNT IN G16
IF I16=NO & K16=YES & N16=YES THEN SHOW AMOUNT IN F16
IF NONE OF THESE THEN SHOW AMOUNT OF 0

This is what I have tried but failed miserably

=IF(AND(I16="yes",K16="yes",N16="yes",F16,0),IF(AND(I16="yes,K16="yes",N16="no",G16,),IF(AND(I16="no",K16="yes",N16="yes",F16,0)

Please can anyone tell me where I am going wrong.

Thanks Amanda
 
C

Crazy Pete

Hi, Amanda.

A couple of commas in the wrong place, I believe. try this:
=IF(AND(I16="yes",K16="yes",N16="yes"),F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,IF(AND(I16="no",K16="yes",N16="yes"),F16,0)))

Regards,
Pete.
 
B

bj

try
=if(K16<>"yes",0,If(N16="yes",F16,If(I16="yes",G16,0)))

or if you really prefer using the if and

IF(AND(I16="yes",K16="yes",N16="yes"),F16,IF(AND(I16="yes,K16="yes",N16="no"),G16,IF(AND(I16="no",K16="yes",N16="yes"),F16,0)))
 
T

Teethless mama

=IF(AND(OR(I16={"yes","no"}),K16="yes",N16="yes"),F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,"??"))
 
T

Teethless mama

correction
change "??" to 0 (no quote)

Teethless mama said:
=IF(AND(OR(I16={"yes","no"}),K16="yes",N16="yes"),F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,"??"))
 
D

Don Guillett

g h i j k L M
Total due deposit due date paid date amt paid tot paid comm
100 10 1-Jun 20-Jun 50 60 1.5

m3=IF(J3-I3>21,0,IF(H3+K3>=G3,G3,H3)*0.15)
 
D

Don Guillett

try this to pay on deposit anyway if over 21 days.
=IF(J3-I3>21,H3,IF(H3+K3>=G3,G3,H3))*0.15
 
A

Amanda

Thanks Guys!
Works Perfect!


Crazy Pete said:
Hi, Amanda.

A couple of commas in the wrong place, I believe. try this:
=IF(AND(I16="yes",K16="yes",N16="yes"),F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,IF(AND(I16="no",K16="yes",N16="yes"),F16,0)))

Regards,
Pete.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top