Evaluate IF(OR is changing all values

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,964
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
3MaybeBad
4MaybeGood
5MaybeBad
6DefinitelyGood
7DefinitelyBAD
8MaybeBAD
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

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,964
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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. :rolleyes:

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
Joined
Jun 12, 2014
Messages
79,649
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 7, 2011
Messages
3,964
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
79,649
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 7, 2011
Messages
3,964
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
1 OR 1 does not equal 2. ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,649
Office Version
  1. 365
Platform
  1. Windows
It does when it's an array calculation. :)
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,596
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top