Jagwarman
asked on
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()
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
Formula I am using is:
=IF(L2<0.01,IF(G2<>TODAY()
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Did you expect another result?
ASKER
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.
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.
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".
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".
Did you try my formula?
ASKER
Hi Syed
I did and that didn't work either but I think it is my PC that has the problem
I did and that didn't work either but I think it is my PC that has the problem
@ Saqib Husain: if the values in the M column are positive and with double decimals, then your formula is equivalent with ="Keep"
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:
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")ASKER
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
ASKER
Maybe its my pc butit's still not working I have uploaded an example
IF-AND.xlsx