Max Average formula

[MathTeacher77]

I'm creating a gradebook and want to allow teachers to put in two grades but only average the highest grade for any given assignment. So two rows of grades and one row using the Max formula, then I average that entire row. The problem comes when people insert columns or delete things...either way my formula keeps getting destroyed.



Is there a way to have just one formula to find the average of the max value without the row of individual max values? Hopefully this makes sense... I've included a copy of what I'm trying to do.

Thanks in advance!

Keep in mind since it's a gradebook it could have 150+ assignments throughout a yearlong course.

 

[Fluff]

Ok, how about

=AVERAGE(BYCOL(filter(d8:xx9,d5:xx5<>"test"),LAMBDA(bc,IF(SUM(bc),MAX(bc),""))))

 

[MathTeacher77]

Ok, how about

=AVERAGE(BYCOL(filter(d8:xx9,d5:xx5<>"test"),LAMBDA(bc,IF(SUM(bc),MAX(bc),""))))

You're a genius Fluff! Thank you very much!!

 

[Fluff]

Glad we could help & thanks for the feedback