Can you do an IF statement on a range?

[Blackcloud_9]

Can you do an IF statement on a range? See statements below (Please forgive any errors 1st time posting)

If I try:
=IF(E13:E21="","",(IF(AND(D13:D20>=62,D21>=86.02),"PASS","FAIL")))
or
=IF(E13:E21="","",(IF(AND(D13>=62,D14>=62,D15>=62,D16>=62,D17>=62,D18>=62,D19>=62,D20>=62),"PASS","FAIL")

Each one gives me a #Value error

Also on each individual PASS/FAIL cell (See H17 for example) I have “=IF(G17="","",(IF(G17>=31,"PASS","FAIL")))”, so if there isn’t an entry in the Pressure column, the PASS/FAIL cell will remain empty. (This works)

I would like to apply that to D21 and G21. Do I need to create a huge IF-OR-AND statement or is there a more concise way?

Automated Data Sheet redacted.xlsx
	A	B	C	D	E	F	G	H
1
2
3			Inner Tray			Outer Tray
4
5			Test Selected:__________			Test Selected:__________
6			Flow:	Sensitivity:		Flow:	Sensitivity:
7			Prefill:	Units:		Prefill:	Units:
8			ID	Pressure	Pressure ≥ 62.0	ID	Pressure	Pressure ≥ 31.0
9		(in H2O)			(P or F)			(P or F)
10		Beginning	A1	90	PASS	A1	23	FAIL
11			A2	120	PASS	A2	44	PASS
12			B1	62	PASS	B1	15	FAIL
13			B2	66	PASS	B2	0	FAIL
14		End	A1	99	PASS	A1	-6	FAIL
15			A2	105	PASS	A2	87	PASS
16			B1	105	PASS	B1	2112	PASS
17			B2	77	PASS	B2
18			Mean	90.50	PASS	Mean	325.00	PASS
19			Inner Tray:	86.02		Outer Tray:	44.07
20
21			Individual and Mean values (Pass/Fail)	PASS		Individual and Mean values (Pass/Fail)
Sheet2

Cell Formulas
Range	Formula
E10:E17	E10	=IF(D10="","",(IF(D10>=62,"PASS","FAIL")))
H10:H17	H10	=IF(G10="","",(IF(G10>=31,"PASS","FAIL")))
D18,G18	D18	=AVERAGE(D10:D17)
E18	E18	=IF(D18="","",(IF(D18>=86.02,"PASS","FAIL")))
H18	H18	=IF(G18="","",(IF(G18>=44.07,"PASS","FAIL")))
D21	D21	=IF(AND(D10>=62,D11>=62,D12>=62,D13>=62,D14>=62,D15>=62,D16>=62,D17>=62,D18>=86.02),"PASS","FAIL")

 

[gaz_chops]

The formula needs to be entered as an array using Ctrl, Shift & Enter

also, the 1st one won't work, maybe try

=IF(COUNTA(E13:E21),IF(AND(D13:D20>=62,D21>=86.02),"PASS","FAIL"),"")

 

[Blackcloud_9]

Thank you for your reply.
The array works but the "empty cell" part < IF(E13:E21="","", > or < IF(COUNTA(E13:E21), >

does not.

 

[gaz_chops]

Try

=IF(COUNTA(E13:E21)<9,"",IF(AND(D13:D20>=62,D21>=86.02),"PASS","FAIL"))

Enter as an array.

 

[Blackcloud_9]

The " =IF(COUNTA(E13:E21)<9 " did not work.
But I tried the a COUNTBLANK and that did the trick.
=IF(COUNTBLANK(E13:E21),"",IF(AND(D13:D20>=62,D21>=86.02),"PASS","FAIL")) - as an array

Thank you for the help. The biggest help was the array. Haven't use it for awhile.

 

[gaz_chops]

You're welcome, glad you got it working.