When testing for multiple conditions, OR logic is used to determine if ANY condition in the test is TRUE.
The only time OR function evaluates to FALSE is if all conditions in a test are false
=OR( 2>5, 6>5 ) will evaluate to TRUE though the first logical test (2>5) is FALSE
=OR( 2>5, 5>6 ) will evaluate to FALSE because the two conditions are FALSE
Interestingly, the OR function can be represented by the plus (+) Sign
=(2>5) + (6>5) =TRUE
= (2>5) + (5>6 ) =FALSE
How is this possible?
In Excel, the numeric equivalent of a FALSE Boolean value is Zero (0) while any other number (positive or negative) is equivalent to a TRUE Boolean Value.
Therefore, =(2>5) + (6>5) =TRUE translates to 0 + 1 =1
And =(2>5) + (5>6) =TRUE translates to 0 + 0 =0
The one major difference between the plus (+) symbol and OR function is that OR function is not able to Spill logically!
See the examples below for a better explanation.
๐ ๐๐๐๐๐ ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง ๐ฎ๐ฌ๐ข๐ง๐ ๐๐ ๐๐จ๐ ๐ข๐
The FILTER function is one of the new dynamic array functions that have spill capability.
Spill means that a formula has resulted in multiple values, and those values have been placed in the neighboring cells.
In the below example, filter budget data for the year 2021 or 2022
=FILTER(Budget,OR(Budget[Year]=F2,Budget[Year]=F1)) results into an error #VALUE
=FILTER(Budget,(Budget[Year]=F2)+(Budget[Year]=F1)) results in the correct filtered data
To make OR function work with the FILTER function, you have to use the LAMBDA helper columns
=FILTER(Budget, MAP(Budget[Year], LAMBDA(a,OR(a=F2,a=F1)) ))
IF ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง ๐ฎ๐ฌ๐ข๐ง๐ ๐๐ ๐๐จ๐ ๐ข๐
Just like the FILTER function above, the plus (+) symbol works if you select an array of data but unlike the FILTER function, the OR function does not result in an error but incorrect results.
For example, Adjust the budget by 10% for all positions in the year 2022 or 2021
SUMIFS ๐ ๐ฎ๐ง๐๐ญ๐ข๐จ๐ง ๐ฎ๐ฌ๐ข๐ง๐ ๐๐ ๐๐จ๐ ๐ข๐
You cannot use OR function with SUMIFS but you can use the plus (+) sign.
By default conditions in SUMIFS follow the AND logic.
Here is how to use the OR logic
=SUMIFS(Budget3[Expenses],Budget3[Year],F2)+ SUMIFS(Budget3[Expenses],Budget3[Year],F1)
Alternatively
=SUM(SUMIFS(Budget3[Expenses],Budget3[Year],F1:F2))
CONCLUSION
If you will ever want to test if ANY condition in a set is TRUE, then OR logic is what you require.
The only other question to ask yourself is, “Do you want your results to spill or not?”
If you need Spilling results, Use the plus (+) symbol else use the OR function.
Recent Comments