• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

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
Asked:
Jagwarman
  • 5
  • 4
  • 2
2 Solutions
 
Dan CraciunIT ConsultantCommented:
I believe this is what you're looking for:
=IF(AND(L2<0.01, G2<>TODAY()), "Remove", "Keep")

Open in new window

HTH,
Dan
0
 
Saqib Husain, SyedEngineerCommented:
=IF(AND(L2<>0,L2<0.01,G2<>TODAY()),"Remove","Keep")
0
 
JagwarmanAuthor Commented:
Dan,

Maybe its my pc butit's still not working I have uploaded an example
IF-AND.xlsx
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Dan CraciunIT 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
 
JagwarmanAuthor 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
 
Dan CraciunIT 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
 
Saqib Husain, SyedEngineerCommented:
Did you try my formula?
0
 
JagwarmanAuthor Commented:
Hi Syed

I did and that didn't work either but I think it is my PC that has the problem
0
 
Dan CraciunIT ConsultantCommented:
@ Saqib Husain: if the values in the M column are positive and with double decimals, then your formula is equivalent with ="Keep"
0
 
Dan CraciunIT 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")

Open in new window

0
 
JagwarmanAuthor 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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now