Solved

IF AND formula not working

Posted on 2014-03-24
11
105 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 46
Excel Conditional Median Problem 1 15
VBA: insert new column and re-adapat string with lower letterS 4 30
VLOOKUP 6 18
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

823 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