IF Statement

[Ardy]

Hello All:
I have this formula =IF(AH2>AQ2,"Y",IF(AH2<AQ2,"N",IF
(AH2="ND","N","N")))
it works pritty good with the exception of the ND part. when I test
it I expect to get a N when entering ND in AH2 but get Y. Can any
body show me where I went wrong.

Ardy

 

[GBC]

I am trying to create/use a formula to count the date range between a
specific period in one column and whether another column contains the
specific test i.e...ICO or WFO I am getting a count back of 1 but it should
be 3 and I have double checked for unkown character spaces in the cells.
=COUNTA((E3:E58>="2/1/2009")*AND(H3:H58="ICO"))

Can someone please help me with this problem????

 

[Dave Peterson]

I think you'll want to share what you have in AH2 and AQ2.

And it looks like you're assigning N to all the "else" portions of your
formula. Is that on purpose or an error in the real formula or an error in the
post?

=IF(AH2>AQ2,"Y","N")
looks equivalent to the formula you posted

 

[Dave Peterson]

=sumproduct(--(e3:e58>=(date(2008,2,1)),--(h3:h58="ico"))

(I couldn't tell if your date was Feb 2, 2009 or Jan 2, 2009--I used Feb 1, 2009
in that formula.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

If you're using xl2007, there's a =countifs() function that you may want to look
at.

 

[ardy]

I think you'll want to share what you have in AH2 and AQ2.

And it looks like you're assigning N to all the "else" portions of your
formula.  Is that on purpose or an error in the real formula or an error in the
post?

=IF(AH2>AQ2,"Y","N")
looks equivalent to the formula you posted

Hello Dave:
Thanks for clarifying the Post problem. I wish I knew how to remove
the CountA, but hay as long as I got you.........

OK My logic is a if statement that evalutes 4 conditions
1- if AH2 is Grather than AQ2 place a Y
2- if AH2 is Less Than AQ2 place a N
3- if AH2 is equal to ND plave an N
4- else place a N

I see your point if 1 is true then 2 and 4 are the same which is else,
and 3 is another if so this should work right =IF(AH2>AQ2,"Y",IF
(AH2="ND","N")). The problem is that if AH2=AQ2 then it also should
be N the above rework returns a Y and also returnes Y for ND

Ardy

 

[Shane Devenshire]

Hi,

Back to the original

=IF(AH2>AQ2,"Y","N")

There is no need to rework anything. It works just like it is above unless
you are not telling us something. Note you have one condtion that returns Y
and all the others return N so there is no need to test each of the other
conditions. You only need additional tests if you have other answers you
want to return, but you have not showed us any.

 

[ardy]

Hi,

Back to the original

=IF(AH2>AQ2,"Y","N")

There is no need to rework anything.  It works just like it is above unless
you are not telling us something.  Note you have one condtion that returns Y
and all the others return N so there is no need to test each of the other
conditions.  You only need additional tests if you have other answers you
want to return, but you have not showed us any.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire









- Show quoted text -

It returns a Y if ND is entered in Cell AH2 it should return a N
which is All other conditions. Maybe becuse ND is Text not
number........

 

[Dave Peterson]

What do you have in AH2?
What do you have in AQ2?

It returns a Y if ND is entered in Cell AH2 it should return a N
which is All other conditions. Maybe becuse ND is Text not
number........

 

[Kevin]

What do you have in AH2?
What do you have in AQ2?







--

Dave Peterson- Hide quoted text -

- Show quoted text -

1- if AH2 is Grather than AQ2 place a Y
2- if AH2 is Less Than AQ2 place a N
3- if AH2 is equal to ND plave an N
4- else place a N

I am bit confused on the third and fourth point, since the output of
both is "N". Do you think you need the third criteria in your
formula..?

 

[Eduardo]

Hi Ardy,
Try

=IF(ISNUMBER(FIND("ND",AH2))=TRUE,"N",IF(AH2>AQ2,"Y","N"))

It works for me

 

[ardy]

1- if AH2 is Grather than AQ2 place a Y
2- if AH2 is Less Than AQ2 place a N
3- if AH2 is equal to ND plave an N
4- else place a N

I am bit confused on the third and fourth point, since the output of
both is "N". Do you think you need the third criteria in your
formula..?- Hide quoted text -

- Show quoted text -

Sorry All for not explaining enough........
AH2 contains Numbers and letters either or. either a number or
letters ND for none detect (results Water Quality-WQ)
AQ2 contains numbers or Null (Detection Limit for Reporting DLR)
The formula (if statement) goes into AL2 and places a Y for Yes N for
No and ND for none detect.
The provbided solution of =IF(AH2>AQ2,"Y","N") works for all
situations with the exeption of ND should return a N It returns a Y
and if AH2=AQ2 it should return a N it returns Y

Ardy

 

[Dave Peterson]

What are in those cells when you don't get what you want?

The exact values--not the rules for what goes in them.

 

[ardy]

Hi Ardy,
Try

=IF(ISNUMBER(FIND("ND",AH2))=TRUE,"N",IF(AH2>AQ2,"Y","N"))

It works for me






- Show quoted text -

Thanks Eduardo:
Works like Charm, I guess the part I was missing was ISNUMBER(FIND
("ND",AH2))=TRUE,"N", which looks into the value and if ND is True
Places a N and the rest is history.......

I do Apriciate from all helping me on this....

Ardy

 

[Eduardo]

Your welcome
If this was helpful please say yes at the bottom of the screen. thank you