# Evaluate IF(OR is changing all values

#### johnnyL

##### Well-known Member
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
ABC
1DefinitelyGood
2GoodGood
4MaybeGood
6DefinitelyGood
9DefinitelyGood
10DefinitelyGood
11
Sheet1

Code I am trying to use:
VBA Code:
``````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:
VBA Code:
``````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``````

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Alex Blakenburg

##### MrExcel MVP
Try replacing "*" with "+"

#### johnnyL

##### Well-known Member
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.

VBA Code:
``````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

##### MrExcel MVP, Moderator
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
ABCD
1OrAnd
20000
30110
41010
51121
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=A2+B2
D2:D5D2=A2*B2

#### johnnyL

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
1 OR 1 does not equal 2.

#### Fluff

##### MrExcel MVP, Moderator
It does when it's an array calculation.

#### Alex Blakenburg

##### MrExcel MVP
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
ABCD
1What Excel considers True or False in an IF statement context
2
3Value=IF(A4,TRUE,FALSE)Comment
4FALSE<-- Value is empty cell
51TRUE
62TRUE
73TRUE
80FALSE
9-1TRUE
10-2TRUE
11
12Logical Data Type as 1 or 0
13
14T/Fx 1 ie * 1+ 0-- (2 minus signs) Terminology "Double Unary"
15TRUE111
16FALSE000
17
18Using the logic with CountIf
19
20ValuesNo to CountCcount ResultIF Statement
21123TRUE
22231TRUE
232100FALSE
242
253
264
274
Sheet1
Cell Formulas
RangeFormula
B3B3=FORMULATEXT(B4)
B4:B10B4=IF(A4,TRUE,FALSE)
B15:B16B15=A15*1
C15:C16C15=A15+0
D15:D16D15=--A15
C21:C23C21=COUNTIFS(\$A\$21:\$A\$27,B21)
D21:D23D21=IF(COUNTIFS(\$A\$21:\$A\$27,B21),TRUE,FALSE)

Replies
20
Views
437
Replies
3
Views
138
Replies
3
Views
99
Replies
0
Views
101
Replies
0
Views
146

### Forum statistics

1,181,322
Messages
5,929,281
Members
436,660
Latest member
Mouseinalabyrinth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back