• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested If/And function help

K

Kristen_cancerkiller

Member
Hello, I am working on a document and I need to return values based on several fields. What I need is if AA16 is greater than AA15, and if z16 is greater than 3.00 then "yes" else "no". I think that is what I have in my formula below but I seem to be getting the opposite results. What am I doing wrong?

Code: 
=IF((AND(AA16>$AA$15,Z16>3)),"Yes","No")
 
Hi ,

The formula should be :

=IF(AND(AA16 > AA15 , Z16 > 3) , "Yes" , "No")

which is what you have.

How do you say you are getting the opposite results ?

Narayan
 
For example, on my first row, AA16=16.15, AA15=10.00, Z16=3.10. On all counts, this should be a Yes response but I'm getting a 'No' response. But AA16 16.15) is greater than AA15 (10.00) and Z16 (3.10) is greater than 3. What am I missing?
 
Hmmm... they are formulas... I just checked them all and they are 'Time' format numbers? Would they be considered text? If they are, how can I do this another way? Any thoughts?
 
Hi ,

The correct way to decide whether a cell contains a number or text is by using the TYPE function on the cell contents.

Suppose you have 16.15 in cell AA16.

In any unused cell , put in the formula : =TYPE(AA16) and see what it displays ; if it displays 1 , then AA16 contains a number ; if it displays 2 then AA16 contains text that looks numeric.

Narayan
 
So AA15 displays 2... the others display 1. So I had just entered that value in AA15... but in my formula I even tried using 10:00 instead of AA15... wouldn't that have corrected the issue?
 
Hi ,

Generally speaking a cell can have text in it in two ways ; one you have actually entered a number as text , by prefixing it with the apostrophe symbol ' , and the other is by first formatting a cell as Text and then entering the number in it.

So once you format a cell as Text , even entering a number will not change anything. Reformat the cell as General and then enter data into it.

Narayan
 
Fixed that, however, I am getting the correct results if instead I use the 'less than' symbol where I currently have the 'greater than' symbol. This formula is giving me the correct 'Yes' response

=IF(AND(AA16<10,Z16<3),"Yes","")
 
Exactly, it doesn't... but I know what the results should be and that is the only way that the results work out correctly? I think maybe I will go to bed for now and try again tomorrow. :) Thank you so much for all of your help on this! I'll post here again tomorrow if I still can't get it to work right.
 
There's a very important difference between:
=IF((AND(AA16>$AA$15,Z16>3)),"Yes","No")
in the first message
and:
=IF((AND(AA16>AA15,Z16>3)),"Yes","No")
in the second message.

The first always uses cell AA15 for comparison on all the rows, the second compares adjacent rows in coumn AA (I'm assuming you're copying down).
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top