Hello All:
I have this formula =IF(AH2AQ2,"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

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

"Ardy" wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

=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 he
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.

GBC wrote:

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

"Ardy" wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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


--

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(AH2AQ2,"Y","N")
looks equivalent to the formula you posted

Ardy wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

--

Dave Peterson

On Feb 24, 9:09*am, Dave Peterson wrote:
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(AH2AQ2,"Y","N")
looks equivalent to the formula you posted

Ardy wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

--

Dave Peterson

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(AH2AQ2,"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

Hi,

Back to the original

=IF(AH2AQ2,"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


"ardy" wrote:

On Feb 24, 9:09 am, Dave Peterson wrote:
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(AH2AQ2,"Y","N")
looks equivalent to the formula you posted

Ardy wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

--

Dave Peterson

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(AH2AQ2,"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

On Feb 24, 10:09*am, Shane Devenshire
wrote:
Hi,

Back to the original

=IF(AH2AQ2,"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



"ardy" wrote:
On Feb 24, 9:09 am, Dave Peterson wrote:
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(AH2AQ2,"Y","N")
looks equivalent to the formula you posted

Ardy wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

--

Dave Peterson

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(AH2AQ2,"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- Hide quoted text -

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

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

ardy wrote:

On Feb 24, 10:09 am, Shane Devenshire
wrote:
Hi,

Back to the original

=IF(AH2AQ2,"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



"ardy" wrote:
On Feb 24, 9:09 am, Dave Peterson wrote:
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(AH2AQ2,"Y","N")
looks equivalent to the formula you posted

Ardy wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

--

Dave Peterson

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(AH2AQ2,"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- Hide quoted text -

- 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

Hi Ardy,
Try

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

It works for me

"Ardy" wrote:

Hello All:
I have this formula =IF(AH2AQ2,"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

On Feb 24, 11:58*am, Eduardo
wrote:
Hi Ardy,
Try

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

It works for me



"Ardy" wrote:
Hello All:
I have this formula =IF(AH2AQ2,"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- Hide quoted text -

- 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

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

"ardy" wrote:

On Feb 24, 11:58 am, Eduardo
wrote:
Hi Ardy,
Try

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

It works for me



"Ardy" wrote:
Hello All:
I have this formula =IF(AH2AQ2,"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- Hide quoted text -

- 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