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.

 

[awoohaw]

what version of excel are you using? (Please add this to your profile so the forum can see it and provide a solution that is available in your version).

And welcome to the forum.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

This is a solution for Excel 365:

Book1
	A	B	C	D	E	F	G
1
2			Test1	Test2	Test3	Test4	Test5
3		Original	91	75	77	74	87
4		Retake	79	95	95	87	90
5
6		average of max	91.6
Sheet4

Cell Formulas
Range	Formula
C6	C6	=AVERAGE(BYCOL(C3:G4,LAMBDA(a,MAX(a))))

 

[Fluff]

Another option for 365

=AVERAGE(BYCOL(H1:K2,LAMBDA(bc,IF(SUM(bc),MAX(bc),""))))

 

[MathTeacher77]

Another option for 365

=AVERAGE(BYCOL(H1:K2,LAMBDA(bc,IF(SUM(bc),MAX(bc),""))))

Thank you both!

That formula is exactly what I was hoping for! thank you! Unfortunately I don't have enough understanding of it (mainly the lamda/array featuer to adapt it to the next step. Can you help me a bit further?

What i had on my own was an averageif formula that found the average as long as it didn't say Test at the top. And another average if that found the average if it DID say test at the top. Then a third box that weighted the two values into a final grade. I'm fine with that setup...there's no danger of those formulas getting erased...or if they do it won't show a grade which is fine.

Here is what I'm looking at...

 

[Fluff]

If your happy with the formulae you already have, then I'm not sure what your asking.

 

[MathTeacher77]

If your happy with the formulae you already have, then I'm not sure what your asking.

I need to be able to average the assignments and reviews in one cell, and average the tests in another cell. I tried combining your formula with the averageif function but I can't make it work. "=AVERAGEIF(D5:S5,"<>test",(BYCOL(D8:R9,LAMBDA(bc,IF(SUM(bc),MAX(bc),"")))))"

 

[Fluff]

If you use the range D8:R9 there are no test results, so still not sure what you are asking for.

 

[MathTeacher77]

If your happy with the formulae you already have, then I'm not sure what your asking.

Maybe this is a better example? Any course in this gradebook will have 10-20 chapters in it so it'll be much bigger than the example I used before or even this one.
As you can A4 I'm averaging row 10 (which uses the max formula) as long as the word TEST is not in row 5. Cell A5 averages the blue row for all values that include the word 'test' in row 5.

Is there a way to use your formula with AverageIF?