IF Function postive and 0 problem

[emina002]

Hi to all,

I got a problem in coding IF Function, here is the condition

Condition:
AP is Negative and AR is Zero-OK
AP is 0 and AR is Positive-OK
AP is Positive and AR is Postive- OK

AP is 0 and AR is Negative-NOT OK
AP is postive and AR is Negative-NOT OK
AP is 0 and AR is 0-NOT OK

Now the problem is, 0 is considered as "Positive" and it just resulted as "OK" if both AP and AR have 0 value. Kindly help!. Thanks


here is my 2 function code, but still resulted to "OK".

1. =IF(OR(AND(LEFT(F4,1)="-",G4=0),AND(F4=0,LEFT(G4,1)<>"-"),AND(LEFT(F4,1)<>"-",LEFT(G4,1)<>"-")),"OK","NOT OK")

2. =IF(AND(LEFT(F4,1)="-",G4=0),"OK",IF(AND(F4=0,LEFT(G4,1)<>"-"),"OK",IF(AND(F4=0,G4=0),"NOT OK",)))

[benishiryo]

hi emina002, it's a little confusing when you talk about AP & AR but used formulas that show F4 & G4. i'm assuming AP is F4 & AR is G4? please upload a sample Excel file if i'm wrong:
=IF(OR(G4<0,COUNTIF(F4:G4,0)=2),"NOT OK","OK")

seems like if AR is negative or both AP & AR is 0, it is NOT OK.

[TMS]

Don't try to isolate the minus sign, use less than zero: <0

For example: ...AND(F4<0, G4=0)...

Regards, TMS

[emina002]

Hi there I am referring for the cells(F4 and G4), see attach workbook for sample, AP and AR amount are just column with amount.

[SDCh]

Try this:

Formula 1

Please Login or Register  to view this content.

Formula 2

Please Login or Register  to view this content.

[emina002]

Hi to all,this problem has been solve.With the formula you given, I just used this

=IF(AND(A2<0,B2=0),"OK",IF(AND(A2=0,B2>0),"OK",IF(AND(A2>0,B2>0),"OK",IF(AND(A2=0,B2=0),"NOT OK",IF(AND(A2>0,B2<0),"NOT OK",IF(AND(A2=0,B2<0),"NOT OK"))))))

[SDCh]

Better you make it simple

Look carefully on your condition: if (AP is positive or zero) and (AR is zero or positive) is "OK", you can write like this " IF(AND(AP>=0,AR>=0),"OK","NOT OK")