MrExcel Publishing
Your One Stop for Excel Tips & Solutions

OR Function

Posted by jac on July 31, 2001 12:46 PM

I would like to have a function that states:

If the sum of these 15 cells equals the value 23, or the value 24, or the value 25, or the vlaue 26, or the value 27, give this symbol, else give this symbol. How can I do this in excel? I can get it to accept 3 OR's but am unable to go any higher.

Posted by Mark W. on July 31, 2001 1:01 PM

The temptation will be to use this:


...but, as you can see is evalutes the sum of your
range repeatedly, and; therefore, is inefficient!
Instead, consider using one of these... array formula as a solution:


Note: Array formulas are entered using the
Control+Shift+Enter key combination. The outermost
braces, {}, are not entered by you -- they're
supplied by Excel in recognition of a properly
entered array formula.

...or a non-array formula:


Posted by Aladin Akyurek on July 31, 2001 1:03 PM


where A1 contains a SUM formula of those "15 cells" you mention.

Given the structure of the numbers {23,24,25,26,27}, it's better to map the problem on AND rather than on OR.



Posted by Mark W. on July 31, 2001 1:09 PM