• Status: Solved
• Priority: Medium
• Security: Public
• Views: 127

# IF AND formula not working

I am trying to use a formula to say: if L2 is less than 0.01 and G2 is not equal to today, Remove it otherwise keep it.

Formula I am using is:

=IF(L2<0.01,IF(G2<>TODAY(),"Remove"),"Keep")

however I appear to be experiencing 2 problems. Even though the amount is 0 and not 0.01 it returns Remove, and if the date is today it returns FALSE where the amount is 0

Appreciate some help with this one.

Thanks
0
Jagwarman
• 5
• 4
• 2
2 Solutions

IT ConsultantCommented:
I believe this is what you're looking for:
``````=IF(AND(L2<0.01, G2<>TODAY()), "Remove", "Keep")
``````
HTH,
Dan
0

EngineerCommented:
=IF(AND(L2<>0,L2<0.01,G2<>TODAY()),"Remove","Keep")
0

Author Commented:
Dan,

Maybe its my pc butit's still not working I have uploaded an example
IF-AND.xlsx
0

IT ConsultantCommented:
So where is the result that is not right? On my computer you have "remove" on rows 10, 12 and 13.

Did you expect another result?
0

Author Commented:
Hi Dan

If the amount is less than or equal to 0.01 and the date is not equal to today the result should be Remove otherwise it should be keep.

The amounts in rows 10, 12 and 13 are zero not 0.01 therefore I would expect it to return keep.
0

IT ConsultantCommented:
If the amount is less than 0.01 - values are 0 so true
the date is not equal to today - values are 2/22/2016, 12/12/2014, 2/20/2015: not equal with 3/24/2014, so true

true AND true = true so the first option in IF is chosen. That option is "Remove".
0

EngineerCommented:
Did you try my formula?
0

Author Commented:
Hi Syed

I did and that didn't work either but I think it is my PC that has the problem
0

IT ConsultantCommented:
@ Saqib Husain: if the values in the M column are positive and with double decimals, then your formula is equivalent with ="Keep"
0

IT ConsultantCommented:
I think it's a logic, not computer problem.
You say
1. if L2 is less than 0.01
2.  "The amounts in rows 10, 12 and 13 are zero not 0.01 therefore I would expect it to return keep"

My formula was for condition no1. If that was not the correct condition and you wanted 2, use this:
``````=IF(AND(L2<>0.01, G2<>TODAY()), "Remove", "Keep")
``````
0

Author Commented:
Iit is a logic problem you are correct and I will be able to use all these formulas going forward so I think it's only right to give you both points. Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.