How to shorten if or formula ?

[pkh135]

Dear Sir and Madam,

Need your suggestion on how to shorten this formula ? The data range is from B11 to B21, at the moment I had to manually add in one by one.

=IF(OR(B11<$I$16,B11>$I$19,B12<$I$16,B12>$I$19,B13<$I$16,B13>$I$19,B14<$I$16,B14>$I$19,B15<$I$16,B15>$I$19,B16<$I$16,B16>$I$19,B17<$I$16,B17>$I$19,B18<$I$16,B18>$I$19,B19<$I$16,B19>$I$19,B20<$I$16,B20>$I$19,B21<$I$16,B21>$I$19),1,0)


Thank you.

 

[Joe4]

Can you please explain in English exactly what it is you are trying to do (I don't like to try to guess the person's intentions by trying to figure out what they are trying to do from a complex formula)?

 

[Fluff]

Maybe
=IF(OR(B11:B21<I16,B11:B21>I19),1,0)

Confirmed with Ctrl Shift Enter, not just Enter

 

[pkh135]

Maybe
=IF(OR(B11:B21<i16,b11:b21>I19),1,0)

Confirmed with Ctrl Shift Enter, not just Enter

Dear Fluff,

I had tried, but doesn't work.
=IF(OR(B11:B21<I16,B11:B21>I19,1,0))

It prompt message too many arguments.

</i16,b11:b21>

 

[pkh135]

Can you please explain in English exactly what it is you are trying to do (I don't like to try to guess the person's intentions by trying to figure out what they are trying to do from a complex formula)?

Dear Joe4,

I had tried to post the full detail with table but not show at forum. So I just copy the formula.
Is ok..let me short briefing about how it's work.


Target value is 100, tolerance -5% is 95 at I16; +5% is 105 at I19.
Data range B11 to B21.
The result show in merge column is C11 to C21
When the data range contain the value less than 95 or more than 105, result will show 1.
If the data range is in between value of 95 to 105, result will show 0.

Data range is in daily, if for 30 days or 3 months result, I had to manually to type it.
So I looking for help for shorten this formula by data range in form of B11:B21 but it doesn't work.

 

[Michael M]

I'd suggest looking at available options here to post data on the forum

https://www.mrexcel.com/forum/about-board/508133-attachments.html
OR
Upload your file to a sharing site, Dropbox for instance, and then post the link back here

 

[salim hasan]

I think you want all numbers < i16 and >i19
If this is true
See this file

Book1
	B	C	D	F	I	J	K	L
10			How_many	List
11	41		7	41
12	12			12
13	48			48		change the value if $I$16 or $I$19
14	6			6
15	13			13
16	28			28	10	Min
17	29			29
18	21
19	42				25	Max
20	31
21	10
Sheet1

Cell Formulas
Range	Formula
D11	{=SUMPRODUCT(($B$11:$B$21<$I$16)+($B$11:$B$21>$I$19))}
F11	{=IF(ROWS($F$11:F11)>$D$11,"",INDEX($B$11:$B$21,SMALL(IF(($B$11:$B$21<$I$16)+($B$11:$B$21>$I$19),ROW($B$11:$B$21)-ROW($B$11)+1),ROWS($F$11:F11))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

​

 

[Phuoc]

Try this:

=IF(OR(MIN(B11:B21)<$I$16,MAX(B11:B21)>$I$19),1,0)

 

[pkh135]

Try this:

=IF(OR(MIN(B11:B21)<$I$16,MAX(B11:B21)>$I$19),1,0)

Dear Phuoc,

It working and convenience for me....
Appreciate for your kind helpful.

Thank you Phuoc.