If, Logical Values.

[samprince]

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 o
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 valu
inserted for any other date.

Thanks

 

[Bearacade]

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

 

[Guest]

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.

 

[samprince]

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

 

[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!!

 

[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)

 

[Bearacade]

=sum(countif(e16:e23,"yes")-countif(e16:e23,"no")

 

[respinosa]

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

 

[samprince]

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.