Solved

# IF AND formula not working

Posted on 2014-03-24
110 Views
Last Modified: 2014-03-24
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
Question by:Jagwarman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 4
• 2
11 Comments

LVL 35

Accepted Solution

Dan Craciun earned 400 total points
ID: 39949943
I believe this is what you're looking for:
``````=IF(AND(L2<0.01, G2<>TODAY()), "Remove", "Keep")
``````
HTH,
Dan
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 100 total points
ID: 39949959
=IF(AND(L2<>0,L2<0.01,G2<>TODAY()),"Remove","Keep")
0

Author Comment

ID: 39949961
Dan,

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

LVL 35

Expert Comment

ID: 39949972
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 Comment

ID: 39949981
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

LVL 35

Expert Comment

ID: 39949986
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

LVL 43

Expert Comment

ID: 39950007
Did you try my formula?
0

Author Comment

ID: 39950012
Hi Syed

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

LVL 35

Expert Comment

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

LVL 35

Expert Comment

ID: 39950022
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 Comment

ID: 39950033
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original souâ€¦
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month2 days, 13 hours left to enroll

#### 752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.