Evaluate IF(OR is changing all values

[johnnyL]

This is similar to this thread.

The difference is I am trying to use Evaluate IF(OR this time instead of Evaluate IF(AND.
Also In the previous thread mentioned, all the values were erased, this time all values are set to the 'true' value.

Is there a trick to get this to work also?

Single cell formula that works:
=IF(OR(A1="Good",B1="Good"),"OK",A1)

Book3
	A	B	C
1	Definitely	Good
2	Good	Good
3	Maybe	Bad
4	Maybe	Good
5	Maybe	Bad
6	Definitely	Good
7	Definitely	BAD
8	Maybe	BAD
9	Definitely	Good
10	Definitely	Good
11
Sheet1

Code I am trying to use:

Sub TestEvaluateIF_OR_V1()
'
' Check values in A1:B10
'
    With Range("A1:A10")
        .Value = Evaluate("=IF(OR(" & .Address & "=""Good""," & .Offset(, 1).Address & _
                "=""Good""),""OK""," & .Address & ")")                                      '   If cell in Column A range = 'Good' OR cell
'                                                                                           '           in Column B range = 'Good' then change
'                                                                                           '           Column A cell to 'OK' Else leave the value as is
    End With
End Sub

Anyone know the 'trick' to get this to with for 'OR' instead of 'AND'?

The solution provided by @StephenCrump in the previously mentioned thread was:

Sub TestEvaluateIF_AND()
'
' Check values in A1:B10    AND needs to be replaced with '*' ;)
'
    With Range("A1:A10")
        .Value = Evaluate("IF((" & .Address & "= ""Definitely"")*(" & _
                .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")             '   If cell in Column A range = 'Definitely' AND cell
'                                                                                           '           in Column B range = 'Good' then change
'                                                                                           '           Column A cell to 'OK' Else leave the value as is
    End With
End Sub

 

[Alex Blakenburg]

Try replacing "*" with "+"

 

[johnnyL]

Dang it! I was actually going to try that before I opened the thread, but I figured the '*' was just a one off.

Besides that, wouldn't it make more sense for '+' to be used for 'AND'? But who said Microsoft makes sense.

Thank you so much @Alex Blakenburg. Once again you have the answer I was looking for.

Sub TestEvaluateIF_OR()
'
' Check values in A1:B10    OR needs to be replaced with '+' ;)
'
    With Range("A1:A10")
        .Value = Evaluate("IF((" & .Address & "= ""Good"")+(" & _
                .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")             '   If cell in Column A range = 'Good' OR cell
'                                                                                           '           in Column B range = 'Good' then change
'                                                                                           '           Column A cell to 'OK' Else leave the value as is
    End With
End Sub

 

[Fluff]

wouldn't it make more sense for '+' to be used for 'AND'?

Not IMO, as it wouldn't work as can be seen below

Fluff.xlsm
	A	B	C	D
1			Or	And
2	0	0	0	0
3	0	1	1	0
4	1	0	1	0
5	1	1	2	1
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=A2+B2
D2:D5	D2	=A2*B2

 

[johnnyL]

Hey @Fluff.

I was referring to the word 'and'.

Example:
5 and 5 = 10
5 + 5 = 10

One dollar and fifty cents = One dollar + fifty cents

I see your way of thinking though.

 

[Fluff]

I was referring to the word 'and'.

Not sure what that has to do with it, in this context and is a logical operator which is the same as multiplying.

 

[johnnyL]

1 OR 1 does not equal 2.

 

[Fluff]

It does when it's an array calculation.

 

[Alex Blakenburg]

Hey @Fluff.

I was referring to the word 'and'.

Example:
5 and 5 = 10
5 + 5 = 10

One dollar and fifty cents = One dollar + fifty cents

I hope you don't mind me taking the opportunity to have a bit of a rant.

I had that very discussion at home.
In general when most people say I want an Apple and an Orange and a Pear, it is additive and converted to maths that implies plus “+”.
So in an IF statement they put AND which then doesn’t work.

I think if the below was explained they might find it easier to remember.
(not professing for this to be 100% accurate)

1) When an arithmetic operator (+, -, * or x, /) is applied to TRUE or FALSE it treats
• TRUE as being 1
• FALSE as being 0

2) In an IF Statement
• 0 is treated the same as FALSE
• BUT any other number is the same as TRUE ie NOT ZERO = TRUE

3) In the instance of wanting to maximise the filtering ie limit the criteria we want AND
in other words we want ALL the conditions to be TRUE
When we know that TRUE is 1 and FALSE is zero, what would make it zero if even one condition is false. That would be multiplication. Condition 1 is TRUE, Condition 2 is FALSE, Condition 3 is TRUE then 1 x 0 x 1 = 0 and hence FALSE

4) In the instance of wanting to include more items, we want to expand the criteria and we want OR, in other words we want ANY of the conditions to be TRUE (and it can be more than one)
This is where it is important to have the background information that “ANY number” not just “1” is treated at being TRUE. This means that we ADD the TRUEs (1s).
Condition 1 is TRUE, Condition 2 is FALSE, Condition 3 is TRUE then 1 + 0 + 1 = 2, this is NOT ZERO and hence TRUE

Sample Data:-

TRUE vs FALSE.xlsm
	A	B	C	D
1	What Excel considers True or False in an IF statement context
2
3	Value	=IF(A4,TRUE,FALSE)	Comment
4		FALSE	<-- Value is empty cell
5	1	TRUE
6	2	TRUE
7	3	TRUE
8	0	FALSE
9	-1	TRUE
10	-2	TRUE
11
12	Logical Data Type as 1 or 0
13
14	T/F	x 1 ie * 1	+ 0	-- (2 minus signs) Terminology "Double Unary"
15	TRUE	1	1	1
16	FALSE	0	0	0
17
18	Using the logic with CountIf
19
20	Values	No to Count	Ccount Result	IF Statement
21	1	2	3	TRUE
22	2	3	1	TRUE
23	2	10	0	FALSE
24	2
25	3
26	4
27	4
Sheet1

Cell Formulas
Range	Formula
B3	B3	=FORMULATEXT(B4)
B4:B10	B4	=IF(A4,TRUE,FALSE)
B15:B16	B15	=A15*1
C15:C16	C15	=A15+0
D15:D16	D15	=--A15
C21:C23	C21	=COUNTIFS($A$21:$A$27,B21)
D21:D23	D21	=IF(COUNTIFS($A$21:$A$27,B21),TRUE,FALSE)