# OR statement in an array (How to convert using NOT)

#### Phutile

##### Board Regular
How can I change what I would want to use as an OR statement in an array to a NOT statement. I believe that a NOT statement can be used in the OR statements place in an array (correct me if I'm wrong). I saw it in a book at Barnes & Noble (WISH I BOUGHT THE BOOK NOW!), but i just can't remember how to write it. Can someone help with a simple example?
Thanks

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
On 2002-08-28 06:55, Phutile wrote:
How can I change what I would want to use as an OR statement in an array to a NOT statement. I believe that a NOT statement can be used in the OR statements place in an array (correct me if I'm wrong). I saw it in a book at Barnes & Noble (WISH I BOUGHT THE BOOK NOW!), but i just can't remember how to write it. Can someone help with a simple example?
Thanks

Please write a concise example of what you would like to do.

Here is my formula:
{=IF(U2='160379'!\$P\$5:\$R\$18,IF(OR(Dept160!S2>'160379'!\$B\$5:\$D\$18+'160379'!\$H\$5:\$J\$18,Dept160!S2<'160379'!\$B\$5:\$D\$18-'160379'!\$H\$5:\$J\$18),""),"")}
It is fugly. I'm sure there is a better way to "flag" a cell that contains my conditions. I'm not even sure I can go about using an array like I am (\$B\$5:\$D\$18+\$H\$5:\$J\$18)???? I just want to see if I can change that OR statement to a NOT(NOT() something to test it.
Thanks.

any suggestions? i'm stumped

On 2002-08-28 07:35, Phutile wrote:
any suggestions? i'm stumped

I'm not sure what that array formula is supposed to do. Care to elaborate?

By the way, NOT (in propositional logic) applies to single 'sentences'...

Here is the formula I'm at now:
{=IF(U2='160379'!\$P\$5:\$R\$18,IF(NOT(Dept160!S2<'160379'!\$B\$5:\$D\$18+'160379'!\$H\$5:\$J\$18),IF(NOT(Dept160!S2>'160379'!\$B\$5:\$D\$18-'160379'!\$H\$5:\$J\$18),TRUE,""),""),"")}
It doesn't work...but I want it to! =P
What I'm trying to do...I want it to match up with an identifier (U2='160370'!P5:R18), then I want it to check for ouliers by checking if S2 is greater than or less than '160379'!B5+-H5, and give TRUE if it is outside that range and "" if not. However, I most likely am screwing up because I have three columns that I am looking at...and I want excel to know that if you find the match in one of the columns, then go left or right that many columns and use those numbers. So to say....my identifiers are in columns P,Q,and R. I have three other columns with the Averages (B,C,and D), and then three other columns with the StDev (H,I,and J). I want to have my formula check the identifier and match, then if that value is in ...say P, then it will check if S2 is > or < B5+-H5 ...say Q, then it will check if S2 is > or < C5+-I5 ...say R, then it will check if S2 is > or < D5+-J5. Maybe I'm being too greedy in one formula. Can you help Aladin? If you need to see more, I'll email an example.

Thanks

Replies
7
Views
398
Replies
3
Views
331
Replies
6
Views
668
Replies
4
Views
143
Replies
4
Views
420

1,221,525
Messages
6,160,329
Members
451,637
Latest member
hvp2262

### 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