Solved

IF AND formula not working

Posted on 2014-03-24
11
99 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
Comment
Question by:Jagwarman
  • 5
  • 4
  • 2
11 Comments
 
LVL 34

Accepted Solution

by:
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")

Open in new window

HTH,
Dan
0
 
LVL 43

Assisted Solution

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

Author Comment

by:Jagwarman
ID: 39949961
Dan,

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

Expert Comment

by:Dan Craciun
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

by:Jagwarman
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Expert Comment

by:Dan Craciun
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

by:Saqib Husain, Syed
ID: 39950007
Did you try my formula?
0
 

Author Comment

by:Jagwarman
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 34

Expert Comment

by:Dan Craciun
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 34

Expert Comment

by:Dan Craciun
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")

Open in new window

0
 

Author Comment

by:Jagwarman
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now