Blackcloud_9
New Member
- Joined
- Jun 11, 2020
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
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?
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") |