Identify Denominators in Average XLOOKUP

[jbiehl]

Good Morning!
I'm pulling grades from certain assignments using the formula below.
Right now, it's averaging scores found in 'Sep.Grades' that match the first string in CN for given student (in A2) which I thought was PERFECT!

However, I just recently found out that not all assignments entered in 'Sep.Grades' have a denominator of 100. So if a student earned a 5/5, the 5 was essentially being averaged in as a 5%. I need to find a way to average these grades in a way in which the 5/5 would represent 100%. The only location that gives the denominator is in the name of the assignment o for example: "1.05 Quiz: <Numeric MaxPoints:5 Category:Unit 1>". The denominator will not always be in the same location within the text.

The one thing that might be my saving grace is that the denominator always follows this sting "MaxPoints:"

You have solved nearly every question I've had with something that is much more simple than I was making it, but I'm sort of thinking this might be a VBA problem.

Here's the original code that treated all points as if they had a denominator of 100.

=IFERROR(AVERAGEIF(Sep.Grades!$1:$1,"*"&LEFT(CN$1,FIND(" ",CN$1)-1)&"*",XLOOKUP($A2,Sep.Grades!$A:$A,Sep.Grades!$1:$1048576)),"")


All ideas are appreciated!
Thanks!

 

[jbiehl]

Amazing! Thank you so much!

 

[jbiehl]

Awesome!

Thanks for the update. I had wondered about the approach that might be necessary for other months, since the month was part of the worksheet name (and hardwired into the formula). Ideally it would be good to eliminate the INDIRECT functions since they are volatile and can lead to slow performance, but that might be an acceptable tradeoff for the convenience of operating directly on the input worksheets without making any other modifications. I see that the following construction is used several times for extracting the 3-character month designation:
LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)
I believe that can be shortened to...
MID(KA$1,FIND(" ",KA$1)+1,3)

I removed those, and used a UDF for INDIRECT, which seems to be working!

Public Function IndirectNotVolatile(sheetName As String, sheetRange As Range) As Variant
    Set IndirectNotVolatile = Worksheets(sheetName).Range(sheetRange.Address)
End Function

Now I have this longer, less volatile formula:

=IFERROR(SUM(IFERROR( ISNUMBER(SEARCH(LEFT(DG$1,FIND(" ",DG$1)-1),IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",A$1:ZZ$1)))*XLOOKUP($A10,IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$A:$A),IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$1:$1048576)),""))/SUM(IFERROR(ISNUMBER(SEARCH(LEFT(DG$1,FIND(" ",DG$1)-1),IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$A$1:$ZZ$1)))*ISNUMBER(XLOOKUP($A10,IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$A:$A),IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$1:$1048576)))*LEFT(SUBSTITUTE(MID(IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$A$1:$ZZ$1),FIND("MaxPoints:",IndirectNotVolatile(MID(DG$1,FIND(" ",DG$1)+1,3)&".Grades",$A$1:$ZZ$1)&"MaxPoints:")+10,9)," ",REPT(" ",9)),9),"")),"")

Is there any way that you know of to add "MaxPoints:" in the criteria of the numerator? There are some columns with the standard code that should not be included in the calculations, and those don't have "MaxPoints:". It pulls the values in these columns for the student, since it contains the code.

I tried doing a 2nd FIND, but of course it was then an or condition instead of and.

Also, you might have noticed - I reverted to the full "MaxPoints:" upon realizing that there could be an assignment name that includes "s:".