If, Logical Values.

I have one column, with dates in *50,000 over a month.

I want to create another column in column D which enters a value Yes or No depending on what date corresponds in Column A

I would usually use
=IF(A9="01/05/2006","Yes","No") to get a Yes in Column D

However; I want to have multiple dates in this Formula,

and I can't get dates to work with 'IF' Formulas.

So If for instance I wanted a yes in Column D for all dates 01/05/06, 03/05/06 & 16/05/06 what would the formula. A 'No' would be the value inserted for any other date.

Thanks.

Hi samprince

I think your problem was with the OR part, so how about this

=IF(OR(A9="01/05/2006",A9="03/05/06",A9="16/05/06"),"Yes","No")

If you are having problems with dates as texts, check out the DATEVALUE
function.
--
Allllen


"samprince" wrote:

>
> I have one column, with dates in *50,000 over a month.
>
> I want to create another column in column D which enters a value Yes or
> No depending on what date corresponds in Column A
>
> I would usually use
> =IF(A9="01/05/2006","Yes","No") to get a Yes in Column D
>
> However; I want to have multiple dates in this Formula,
>
> and I can't get dates to work with 'IF' Formulas.
>
> So If for instance I wanted a yes in Column D for all dates 01/05/06,
> 03/05/06 & 16/05/06 what would the formula. A 'No' would be the value
> inserted for any other date.
>
> Thanks.
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=551935
>
>

=IF(OR(A9=Date(2006,5,1),A9=Date(2006,05,03),A9=Date(2006,05,16)),"Yes","No")

"samprince" wrote:

>
> I have one column, with dates in *50,000 over a month.
>
> I want to create another column in column D which enters a value Yes or
> No depending on what date corresponds in Column A
>
> I would usually use
> =IF(A9="01/05/2006","Yes","No") to get a Yes in Column D
>
> However; I want to have multiple dates in this Formula,
>
> and I can't get dates to work with 'IF' Formulas.
>
> So If for instance I wanted a yes in Column D for all dates 01/05/06,
> 03/05/06 & 16/05/06 what would the formula. A 'No' would be the value
> inserted for any other date.
>
> Thanks.
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=551935
>
>

[QUOTE=Paul Mathews]=IF(OR(A9=Date(2006,5,1),A9=Date(2006,05,03),A9=Date(2006,05,16)),"Yes","No")

"samprince" wrote:
[color=blue]


You Sir are a legend.

I have a similiar question.

What I'm trying to accomplish is this, if I put the word Yes in cells E16:E21, then E22 will total the numbers of Yes, Yes to Equal "1". However, if No is put in cells E16:E21, the word "No" equals "-1" and subtracts from the total in cell E22.

So the formula would read something like E22 is the total of Cells E16:E21, where Yes=1, and No=-1

E16=Yes=1
E17=Yes=1
E18=No=-1
E19=No=-1
E20=Yes=1
E21=Yes=1
E22=Sum of E16:E21=2

I hope I explained this clearly!!

Originally Posted by respinosa

I have a similiar question.

What I'm trying to accomplish is this, if I put the word Yes in cells E16:E21, then E22 will total the numbers of Yes, Yes to Equal "1". However, if No is put in cells E16:E21, the word "No" equals "-1" and subtracts from the total in cell E22.

So the formula would read something like E22 is the total of Cells E16:E21, where Yes=1, and No=-1

E16=Yes=1
E17=Yes=1
E18=No=-1
E19=No=-1
E20=Yes=1
E21=Yes=1
E22=Sum of E16:E21=2

I hope I explained this clearly!!

Ok I think I have it, but is there an easier way?
This is what I have in E22:
=IF(OR(E16="Yes"),"1","-1")+IF(OR(E17="Yes"),"1","-1")+IF(OR(E18="Yes"),"1","-1")+SUM(E16:E21)

Yeah not quite what I was looking for. But thank you, I'll remember that for another time!

tbh, not sure what your query is Respinosa but... stab in the dark [=


If you are trying to work you the number of Yes's in a column and no's in a column ...

What I alwasy do its create two columns along side: and the first and second respectively, I would type:

=IF(e1="yes",1,0)
=IF(e1="np",1,0) then i would fill these down however far they need to go.

=sum(F1:Fx) & =sum(G1:Gx)
And you have the sum of all the Yes's and the Sum of all the No's in two separate cells.

Hope this helps.