| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(AND problem to work out commissions
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(AN D(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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF(AND problem to work out commissions
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. "Amanda" wrote: 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(AN D(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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF(AND problem to work out commissions
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(I 16="yes,K16="yes",N16="no"),G16,IF(AND(I16="no",K1 6="yes",N16="yes"),F16,0))) "Amanda" wrote: 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(AN D(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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF(AND problem to work out commissions
=IF(AND(OR(I16={"yes","no"}),K16="yes",N16="yes"), F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,"??") )
"Amanda" wrote: 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(AN D(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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF(AND problem to work out commissions
correction
change "??" to 0 (no quote) "Teethless mama" wrote: =IF(AND(OR(I16={"yes","no"}),K16="yes",N16="yes"), F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,"??") ) "Amanda" wrote: 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(AN D(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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF(AND problem to work out commissions
try this to pay on deposit anyway if over 21 days.
=IF(J3-I321,H3,IF(H3+K3=G3,G3,H3))*0.15 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... 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-I321,0,IF(H3+K3=G3,G3,H3)*0.15) -- Don Guillett SalesAid Software "Amanda" wrote in message ... 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(AN D(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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF(AND problem to work out commissions
Thanks Guys! Works Perfect! "Crazy Pete" wrote: 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. "Amanda" wrote: 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(AN D(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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i use macros to work out commissions | Excel Worksheet Functions | |||
| using SUMPRODUCT() for commissions | Excel Worksheet Functions | |||
| commissions by range | Excel Worksheet Functions | |||
| how to calculate commissions | Excel Worksheet Functions | |||
| Calculate commissions | Excel Worksheet Functions | |||
Linear Mode
