keep the average blank if one of the cells is empty

[leazhbay]

Good day!

Is there a formula to keep the FINAL GRADE and ACTION TAKEN empty when the 2nd column is empty? both 1st and 2nd column has formula in it.

Thank you!

 

[Cubist]

What are the formulas in J and K ?

 

[leazhbay]

What are the formulas in J and K ?

J : =IF(OR(H14="",I14=""),"",IF(ISERROR(ROUND(AVERAGE(H14,I14),0)),"",ROUND(AVERAGE(H14,I14),0)))
K : =IF(OR(H14="",I14=""),"",IF($D14="","",IF($D14>=75,"PASSED","FAILED")))

I just copied it from another template I had.

this is the original formula I copied for J : =IF(OR(F13="",J13="",N13="",R13=""),"",IF(ISERROR(ROUND(AVERAGE(F13,J13,N13,R13),0)),"",ROUND(AVERAGE(F13,J13,N13,R13),0)))
Picture for reference

 

[leazhbay]

K : =IF(OR(H14="",I14=""),"",IF($D14="","",IF($D14>=75,"PASSED","FAILED")))

my mistake, formula for K i used originally is : =IF(OR($H14="",$I14="",$J14=""),"",IF($J14>=75,"PASSED","FAILED"))

 

[Cubist]

Try:

Book1
	G	H	I	J	K
13	SUBJECTS	1st	2nd	Final Grade	Action Taken
14	Practical Research	86	18	52	FAILED
Sheet2

Cell Formulas
Range	Formula
J14	J14	=IF(COUNTA(H14:I14)<2,"",ROUND(AVERAGE(H14:I14),0))
K14	K14	=IF(J14="","",IF(J14>=75, "PASSED", "FAILED"))

 

[Cubist]

This version is better if your H and I are getting "" from formulas.

Book1
	G	H	I	J	K
13	SUBJECTS	1st	2nd	Final Grade	Action Taken
14	Practical Research	86	20	53	FAILED
Sheet4

Cell Formulas
Range	Formula
J14	J14	=IF(SUM(--(H14:I14=""))>0,"",ROUND(AVERAGE(H14:I14),0))
K14	K14	=IF(J14="","",IF(J14>=75, "PASSED", "FAILED"))

 

[leazhbay]

below is the effect that I want to achieve, but when I copy the formula and update it, it doesn't happen

sample.xlsx
	F	G	H	I
11	THIRD QUARTER	FOURTH QUARTER	SECOND SEMESTER FINAL GRADES	REMARK
12
13	85
14	84	85	85	PASSED
Final Semestral Grade

Cell Formulas
Range	Formula
F11	F11	=IF('INPUT DATA'!S8="","",IF('INPUT DATA'!S8="1ST","FIRST QUARTER",IF('INPUT DATA'!S8="2ND","THIRD QUARTER")))
G11	G11	=IF('INPUT DATA'!S8="","",IF('INPUT DATA'!S8="1ST","SECOND QUARTER",IF('INPUT DATA'!S8="2ND","FOURTH QUARTER")))
H11	H11	=IF('INPUT DATA'!S8="","",IF('INPUT DATA'!S8="1ST","FIRST SEMESTER FINAL GRADES",IF('INPUT DATA'!S8="2ND","SECOND SEMESTER FINAL GRADES")))
F13:F14	F13	='1ST'!AJ13
G13:G14	G13	='2ND'!AJ13
H13:H14	H13	=IF(OR(F13="",G13=""),"",IF(ISERROR(ROUND(AVERAGE(F13,G13),0)),"",ROUND(AVERAGE(F13,G13),0)))
I13:I14	I13	=IF(OR($F13="",G13="",$H13=""),"",IF($H13>=75,"PASSED","FAILED"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F13:G62,F64:G113	Cell Value	<75	text	NO
I13:I62	Cell Value	contains "FAILED"	text	NO
H13:H62	Cell Value	<75	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F11:I11	Any value
F12:I113	Any value

 

[leazhbay]

below is the effect that I want to achieve, but when I copy the formula and update it, it doesn't happen

ohhh.. that Data Validation...

 

[Cubist]

I don't follow. Your formulas seem to be working, but the syntax could be more concise. My formulas give the same result as yours.

 

[leazhbay]

I don't understand what I missed here. I want the final grades to be empty if the 2nd quarter is empty.
the table I posted above is my reference, but when I copied it to another sheet, this happens:

sample 2.xlsx
	A	B	C	D	E
13	SUBJECTS	1st	2nd	Final Grade	Action Taken
14	Reading And Writing	94		47	FAILED
CARD SLIP

Cell Formulas
Range	Formula
A14	A14	=PROPER('1ST Q (2)'!$C$10)
B14	B14	='1ST Q'!P13
C14	C14	='2ND Q'!P13
D14	D14	=IF(OR(B14="",C14=""),"",IF(ISERROR(ROUND(AVERAGE(B14,C14),0)),"",ROUND(AVERAGE(B14,C14),0)))
E14	E14	=IF(OR($B14="",C14="",$D14=""),"",IF($D14>=75,"PASSED","FAILED"))