Solved

IF AND formula not working

Posted on 2014-03-24
11
106 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 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