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!

 

[KRice]

Glad to help, and thanks for the additional commentary. Eliminating the helper row is messy, but if the table headings in the Sep.Grades worksheet always contain the terms "MaxPoints" and "Category:", then we can use that information to determine where the score basis for each quiz/assignment is found. And this can allow for the helper row to be eliminated. If you have the formula working now with the helper row (which is Sep.Grades!$D$1:$L$1) and if you've successfully converted my INDEX/MATCH formulation into one that utilizes the more efficient XLOOKUP construction, then you should be able to go through that formula and replace all instances of...
Sep.Grades!$D$1:$L$1
with this expression...
MID(Sep.Grades!$D$2:$L$2,FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)+10,FIND("Category:",Sep.Grades!$D$2:$L$2)-FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)-10)*1

In my tests, this substitution occurs in four places in the formula, so the messy formula becomes even longer. A demonstration of it is shown below, where the first three table entries for Trump, Biden, and Norris use the original suggestion in my post #7, and the next three table entries for the same "students" use this lengthier version of the formula that eliminates the helper row on the Sep.Grades worksheet. Note that I've inserted some line spaces into the formula to make it more readable and to call attention to where the substitutions occur, but you can eliminate those spaces and tighten up the formula if you prefer...that has no affect on performance, only readability.

MrExcel20200827.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	94.2%	80.0%		100.0%	96.7%
3	321654	Biden	Joe	99.2%	100.0%		100.0%	96.7%
4	843921	Norris	Chuck	82.5%	80.0%		100.0%	50.0%
5	123456	Trump	Donald	94.2%	80.0%		100.0%	96.7%
6	321654	Biden	Joe	99.2%	100.0%		100.0%	96.7%
7	843921	Norris	Chuck	82.5%	80.0%		100.0%	50.0%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(SUMPRODUCT(ISNUMBER(SEARCH("*"&LEFT(D$1,FIND(" ",D$1)-1)&"*",Sep.Grades!$D$2:$L$2))*INDEX(N(IF(ISNUMBER(Sep.Grades!$D$3:$L$5/Sep.Grades!$D$1:$L$1),Sep.Grades!$D$3:$L$5/Sep.Grades!$D$1:$L$1,"")),MATCH($A2,Sep.Grades!$A$3:$A$5,0),0))/SUMPRODUCT(ISNUMBER(SEARCH("*"&LEFT(D$1,FIND(" ",D$1)-1)&"*",Sep.Grades!$D$2:$L$2))*INDEX(N(IF(ISNUMBER(Sep.Grades!$D$3:$L$5/Sep.Grades!$D$1:$L$1),IF(Sep.Grades!$D$3:$L$5/Sep.Grades!$D$1:$L$1<>0,1,""),"")),MATCH($A2,Sep.Grades!$A$3:$A$5,0),0)),"")
D5:H7	D5	=IFERROR(SUMPRODUCT(ISNUMBER(SEARCH("*"&LEFT(D$1,FIND(" ",D$1)-1)&"*",Sep.Grades!$D$2:$L$2))*INDEX(N(IF(ISNUMBER(Sep.Grades!$D$3:$L$5/ MID(Sep.Grades!$D$2:$L$2,FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)+10,FIND("Category:",Sep.Grades!$D$2:$L$2)-FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)-10)*1 ),Sep.Grades!$D$3:$L$5/ MID(Sep.Grades!$D$2:$L$2,FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)+10,FIND("Category:",Sep.Grades!$D$2:$L$2)-FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)-10)*1,"")),MATCH($A5, Sep.Grades!$A$3:$A$5,0),0))/SUMPRODUCT(ISNUMBER(SEARCH("*"&LEFT(D$1,FIND(" ",D$1)-1)&"*",Sep.Grades!$D$2:$L$2))*INDEX(N(IF(ISNUMBER(Sep.Grades!$D$3:$L$5/ MID(Sep.Grades!$D$2:$L$2,FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)+10,FIND("Category:",Sep.Grades!$D$2:$L$2)-FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)-10)*1 ),IF(Sep.Grades!$D$3:$L$5/ MID(Sep.Grades!$D$2:$L$2,FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)+10,FIND("Category:",Sep.Grades!$D$2:$L$2)-FIND("MaxPoints:",Sep.Grades!$D$2:$L$2)-10)*1 <>0,1,""),"")),MATCH($A5,Sep.Grades!$A$3:$A$5,0),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Bo_Ry]

Another one with normal enter and no helper

MrExcel.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	94.17%	80.00%		100.00%	96.67%
3	321654	Biden	Joe	99.17%	100.00%		100.00%	96.67%
4	843921	Norris	Chuck	82.50%	80.00%		100.00%	50.00%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(SUMPRODUCT(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),),1/TEXT(MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+2,2),"0;;;\1"),--ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2)))/SUMPRODUCT(ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*ISNUMBER(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),))),"")

 

[Bo_Ry]

Or shorter with Ctrl+Shift+Enter

MrExcel.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	94.17%	80.00%		100.00%	96.67%
3	321654	Biden	Joe	99.17%	100.00%		100.00%	96.67%
4	843921	Norris	Chuck	82.50%	80.00%		100.00%	50.00%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(AVERAGE(IF(ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*ISNUMBER(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),)),INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),)/MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+2,2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

@Bo_Ry, Nice job! I had originally tried with AVERAGE and AVERAGEIF but was not successful. After working through the development of the denominator array with MID, I should have revisited it. There may still be one issue if a quiz has a maximum basis point value that is three digits, such as 100. I believe the "2" in the MID component will lead to "10" being returned...but I don't know if any MaxPoints will be 3 digits long. If so, then a little adjustment to the formula would be necessary.

 

[KRice]

To follow-up with my last point, this adjustment to the second offering from @Bo_Ry in post #13 incorporates a check of the 4th character after the "s" in MaxPoints:
So if a quiz has a maximum possible score of 150 points, then the column heading would include a text string containing MaxPoints:150, and the 4th character after the "s" is a 0. The enhancement here checks whether this character is a number: if so, then 3 characters after the colon are used, otherwise 2 characters are used. This means that examinations/assignments having max points of 1, 2, or 3 digits should be extracted correctly from the text string.

MrExcel20200827.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	94.17%	80.00%		100.00%	96.67%
3	321654	Biden	Joe	99.17%	100.00%		100.00%	96.67%
4	843921	Norris	Chuck	82.50%	80.00%		100.00%	50.00%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(AVERAGE(IF(ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*ISNUMBER(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),)),INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),)/MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+2,IF(ISNUMBER(MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+4,1)*1),3,2)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Bo_Ry]

Up to 9 digits of MaxPoints

MrExcel.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	94.17%	80.00%		100.00%	96.67%
3	321654	Biden	Joe	99.17%	100.00%		100.00%	96.67%
4	843921	Norris	Chuck	82.50%	80.00%		100.00%	50.00%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(AVERAGE(IF(ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*ISNUMBER(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),)),INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),)/LEFT(SUBSTITUTE(MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+2,9)," ",REPT(" ",9)),9))),"")

 

[jbiehl]

These are getting more amazing by the hour!
Thanks to both of you for your help!

This part seems impossible - but it's worth a shot. I realized that in these formulas the scores are converted to percentiles and then averaged. Is it possible to have a weighted average, so that a 30 point assignment and a 5 point assignment don't carry the same weight? Essentially first add the scores of the assignments attached to each standard, then divide them by the max points possible of all of those assignments?

I used the formula from Post#16 and replaced the averageif with sumif, and added sumif to the denominator, as shown below. It obviously didn’t work. The results are below the formula. It froze my Excel twice (hopefully it’s a coincidence) so I couldn’t save the workbook to attach it. I was able to get a few snips though...

 

[KRice]

I was curious about the desired weighting and mentioned in post #6 that the approach described assigns equal weighting to each score. If instead you want each evaluation to be proportionally weighted by its max points, then summing all scores and dividing by the sum of all max points will do that, as you've suggested. Here is one approach using some combination of suggestions offered earlier but relying on the SUM function to obtain the total numerator and another SUM function to obtain the total denominator. Kudos to @Bo_Ry for the improved method to extract the array of MaxPoints needed for the denominator. The numerator is the sum of all scores for a specified student considering every column in the data table where the column heading includes the RL.* text relevant to the summary Roll-up table. The denominator is the sum of all MaxPoints for examinations where the data table column heading includes the RL.* text relevant to the summary Roll-up table AND the specified student has a numeric entry for that examination. Since you have Excel 365, you will probably not need to enter this as an array formula (with Ctrl-Shift-Enter).

MrExcel20200827.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	97.14%	80.00%		100.00%	96.67%
3	321654	Biden	Joe	98.57%	100.00%		100.00%	96.67%
4	843921	Norris	Chuck	77.14%	80.00%		100.00%	50.00%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(SUM(IFERROR( ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),),""))/SUM(IFERROR(ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*ISNUMBER(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),))*LEFT(SUBSTITUTE(MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+2,9)," ",REPT(" ",9)),9),"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jbiehl]

I was curious about the desired weighting and mentioned in post #6 that the approach described assigns equal weighting to each score. If instead you want each evaluation to be proportionally weighted by its max points, then summing all scores and dividing by the sum of all max points will do that, as you've suggested. Here is one approach using some combination of suggestions offered earlier but relying on the SUM function to obtain the total numerator and another SUM function to obtain the total denominator. Kudos to @Bo_Ry for the improved method to extract the array of MaxPoints needed for the denominator. The numerator is the sum of all scores for a specified student considering every column in the data table where the column heading includes the RL.* text relevant to the summary Roll-up table. The denominator is the sum of all MaxPoints for examinations where the data table column heading includes the RL.* text relevant to the summary Roll-up table AND the specified student has a numeric entry for that examination. Since you have Excel 365, you will probably not need to enter this as an array formula (with Ctrl-Shift-Enter).

MrExcel20200827.xlsx
	A	B	C	D	E	F	G	H
1	Student ID	Last Name	First Name	RL.8 September Grades	RL.8.1 September Grades	RL.8.2 September Grades	RL.8.5 September Grades	RL.8.6 September Grades
2	123456	Trump	Donald	97.14%	80.00%		100.00%	96.67%
3	321654	Biden	Joe	98.57%	100.00%		100.00%	96.67%
4	843921	Norris	Chuck	77.14%	80.00%		100.00%	50.00%
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=IFERROR(SUM(IFERROR( ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),),""))/SUM(IFERROR(ISNUMBER(FIND(LEFT(D$1,FIND(" ",D$1)-1),Sep.Grades!$D$2:$L$2))*ISNUMBER(INDEX(Sep.Grades!$D$3:$L$5,MATCH($A2,Sep.Grades!$A$3:$A$5,),))*LEFT(SUBSTITUTE(MID(Sep.Grades!$D$2:$L$2,FIND("s:",Sep.Grades!$D$2:$L$2&"s:")+2,9)," ",REPT(" ",9)),9),"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

This has been working great, thanks!
I did end up adding to it so that I didn't have to type the name of the worksheet. The 2nd string in each header is always the month of the worksheet that it is pulling from, so I added an INDIRECT formula to call the first three letters in the 2nd string and ".Grades" since every tab is in that format - "Sep.Grades", "Oct.Grades", etc. It's pretty massive and slows it down, but now all I have to do is fill in the header.

=IFERROR(SUM(IFERROR( ISNUMBER(FIND(LEFT(KA$1,FIND(" ",KA$1)-1),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1")))*XLOOKUP($A2,INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!A:A"),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1048576")),""))/SUM(IFERROR(ISNUMBER(FIND(LEFT(KA$1,FIND(" ",KA$1)-1),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1")))*ISNUMBER(XLOOKUP($A2,INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!A:A"),INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1048576")))*LEFT(SUBSTITUTE(MID(INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1"),FIND("s:",INDIRECT("'"&LEFT(SUBSTITUTE(MID(KA$1,FIND(" ",KA$1)+1,3)," ",REPT(" ",3)),3)&".Grades"&"'!1:1")&"s:")+2,9)," ",REPT(" ",9)),9),"")),"")

Thanks again!

 

[KRice]

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)