Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / June 2007

Tip: Looking for answers? Try searching our database.

IF(AND problem to work out commissions

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Amanda - 11 Jun 2007 12:53 GMT
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
Crazy Pete - 11 Jun 2007 13:01 GMT
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.

> I am stuck using IF AND Function
>
[quoted text clipped - 25 lines]
>
> Thanks Amanda
Amanda - 11 Jun 2007 16:51 GMT
Thanks Guys!
Works Perfect!

> Hi, Amanda.
>
[quoted text clipped - 33 lines]
> >
> > Thanks Amanda
bj - 11 Jun 2007 13:03 GMT
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)))

> I am stuck using IF AND Function
>
[quoted text clipped - 25 lines]
>
> Thanks Amanda
Teethless mama - 11 Jun 2007 15:03 GMT
=IF(AND(OR(I16={"yes","no"}),K16="yes",N16="yes"),F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,"??"))

> I am stuck using IF AND Function
>
[quoted text clipped - 25 lines]
>
> Thanks Amanda
Teethless mama - 11 Jun 2007 15:28 GMT
correction
change "??" to 0 (no quote)

> =IF(AND(OR(I16={"yes","no"}),K16="yes",N16="yes"),F16,IF(AND(I16="yes",K16="yes",N16="no"),G16,"??"))
>
[quoted text clipped - 27 lines]
> >
> > Thanks Amanda
Don Guillett - 11 Jun 2007 16:22 GMT
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)
Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> I am stuck using IF AND Function
[quoted text clipped - 29 lines]
>
> Thanks Amanda
Don Guillett - 11 Jun 2007 16:43 GMT
try this to pay on deposit anyway if over 21 days.
=IF(J3-I3>21,H3,IF(H3+K3>=G3,G3,H3))*0.15

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>      g  h i j k L M
>      Total due deposit due date paid date amt paid tot paid comm
[quoted text clipped - 36 lines]
>>
>> Thanks Amanda
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.