Checking Values in A range lie between 2 values .

[PheoBlue]

Hello,
I am working on a worksheet at the moment and have run across a small problem.

I have a table/Range with 12 values in it and i am looking to return a True/false, Pass /Fail, based upon the verification that the values lie between the values stored in 2 cells.
The 2 Cells are calculated values of Average +20% and Average -20% , and the test is to verify the values lie within the +/- 20% allowed.

0.81​	0.75​	0.89​	0.75​	0.78​
0.77​	0.65​	0.79​	0.74​	0.88​
0.88​	0.80​	0.77​	0.88​	0.89​

0.96

0.64

So the expected result should look at the cells within the table/Range and as long as the values Lie within 0.96 and 0.64 (+/-20%) it should Return True or Pass ...., etc and if Not it Should Return Fail, False, Etc .....
I have tried a few different things but nothing seems to return what I am looking to produce.
Am looking to do this with a function and not VBA if possible .

Thank You in advance for any assistance you may be able to provide.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E
1	0.81	0.75	0.89	0.75	0.78
2	0.77	0.65	0.79	0.74	0.88
3	0.88	0.8	0.77	0.88	0.89
4
5	0.96	False
6	0.64
Data

Cell Formulas
Range	Formula
B5	B5	=COUNTIFS(A1:E3,">"&A5,A1:E3,"<"&A6)>0

 

[Scott T]

try

Book1
	A	B	C	D	E	F
2	0.81	0.75	0.89	0.75	0.78	1
3	0.77	0.65	0.79	0.74	0.88	6
4	0.88	0.8	0.77	0.88	0.89	-5
5
6	0.96	True
7	0.64	True
8
9	Pass	Pass	Pass	Pass	Pass	Fail
10	Pass	Pass	Pass	Pass	Pass	Fail
11	Pass	Pass	Pass	Pass	Pass	Fail
Sheet1

Cell Formulas
Range	Formula
B6:B7	B6	=ISNUMBER(A6)
A9:F11	A9	=IF(AND(A2<=$A$6,A2>=$A$7),"Pass","Fail")

 

[PheoBlue]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E
1	0.81	0.75	0.89	0.75	0.78
2	0.77	0.65	0.79	0.74	0.88
3	0.88	0.8	0.77	0.88	0.89
4
5	0.96	False
6	0.64
Data

Cell Formulas
Range	Formula
B5	B5	=COUNTIFS(A1:E3,">"&A5,A1:E3,"<"&A6)>0

Thank you for your solution ,
When i am testing it though as you can see is with 2 values outside of the test condition it still comes back as TRUE ?

Ideally what I am looking for is a formula that only returns a TRUE when all of the values in the table lie within the top and bottom limit values.
For example as long as the Values A1:E3 are <=0.95 (B7) and >=0.63 (B8) then the function should return a TRUE if any of the values are outside of the limits it should return as FALSE.

Is there something else I could try?

 

[Fluff]

You have the B8 & B7 the wrong way round in the formula.

 

[PheoBlue]

try

Book1
	A	B	C	D	E	F
2	0.81	0.75	0.89	0.75	0.78	1
3	0.77	0.65	0.79	0.74	0.88	6
4	0.88	0.8	0.77	0.88	0.89	-5
5
6	0.96	True
7	0.64	True
8
9	Pass	Pass	Pass	Pass	Pass	Fail
10	Pass	Pass	Pass	Pass	Pass	Fail
11	Pass	Pass	Pass	Pass	Pass	Fail
Sheet1

Cell Formulas
Range	Formula
B6:B7	B6	=ISNUMBER(A6)
A9:F11	A9	=IF(AND(A2<=$A$6,A2>=$A$7),"Pass","Fail")

Thank you for your solution Also,
When i am testing it as you can see is with 2 values outside of the test condition your solution does Highlight the 2 that fall outside , which is along the lines i am looking for ?

Ideally what I am looking for is a formula that only returns a TRUE when ALL of the values in the table lie within the top and bottom limit values.
For example as long as the Values A1:E3 are <=0.95 (B7) and >=0.63 (B8) then the function should return a TRUE if any of the values are outside of the limits it should return as FALSE.

Is there something else I could try?

 

[PheoBlue]

You have the B8 & B7 the wrong way round in the formula.

HI,
I'm probably doing something wrong but, Have rearranged as per your reply.
When testing it with live Data again i still have a problem where it doesn't recognise that when the value has changed, as you can see from the image.
B2 is now 1.25 which is outside of the 1.03 Maximum yet still shows as true.

Any suggestions ?

 

[Fluff]

OOps, wasn't thinking.
How about
=OR(COUNTIF(A1:E3,">"&B7),COUNTIF(A1:E3,"<"&B8))

 

[PheoBlue]

OOps, wasn't thinking.
How about
=OR(COUNTIF(A1:E3,">"&B7),COUNTIF(A1:E3,"<"&B8))

Thanks ,
It's working, with a true and False I can code the conditional formatting I need for it to display correctly.
.
Thank You for your Patience.

 

[Fluff]

You're welcome & thanks for the feedback