ISBLANK, IF formula help!

[jayjay123]

Hi Experts of Mr Excel.com,

Please let me know if I'm not making sense.
Heres a sample.

• Action 1 - 4 will result in either a "Pass" or "Fail"
• Overall Outcome = Fail (If 70% or below, or if Action 3 results in a Fail)
• Overall Outcome = Pass (if above 70%, and if Action 3 results in a Pass)
• Action 3 - Fail results in automatic Overall fail
• Also, if the template is not yet filled in for Action 1-4 (Pass/Fail), I would like the Overall Outcome to be blank

Can anyone design a formula for this? I tried the following:
=IF(OR(ISBLANK(H43:H44)," ",IF(OR("Fail" = K34:H44),"Fail","Pass"))

H43:H44 = (Pass:Fail) cells

Thanks in advanced!

 

[Joe4]

I cannot say I follow your logic completely, but I see two issues with your formula, namely these parts:
ISBLANK(H43:H44)
and
"Fail" = K34:H44

The issue is ISBLANK and = are not array functions.
Try using the COUNTBLANK and COUNTIF functions instead to return a count of values in that range that are blank or equal to fail, i.e.
IF(COUNTBLANK(H43:H44)=2,...
and
IF(COUNTIF(K34:K44,"Fail")>0,...

 

[jayjay123]

Sorry! i will try and explain again...
What formula can I use for the Overall Outcome...
- Over 70% = Pass
- Less than 70% = Fail or if Action 3 is Fail
- How do I make this cell blank until Action 1-4 has been selected with either Pass/Fail

Thanks!

 

[jtakw]

Hi,

Your question is still a bit unclear, also, what if it's Exactly 70% ?
Because you say:

- Over 70% = Pass
- Less than 70% = Fail or if Action 3 is Fail

Assuming >= 70 % is Pass

Book3.xlsx
	A	B	C
1		out of 25
2	1	25	Pass
3	2	25	Pass
4	3	15	Fail
5	4	25	Pass
6		90.00%	Fail
Sheet1064

Cell Formulas
Range	Formula
C2:C5	C2	=IF(B2/25>=0.7,"Pass","Fail")
B6	B6	=SUM(B2:B5)/100
C6	C6	=IF(COUNTA(C2:C5)=4,IF(OR(B6<0.7,C4="Fail"),"Fail","Pass"),"")