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!

 

[C Moore]

Could you post a line or two from the actual book (minus names of course)?

 

[jbiehl]

Sure thing! Thanks!

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

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
123456	Trump	Donald	17	4		5	29
321654	Biden	Joe	17.25	5		5	29
843921	Norris	Chuck	13.5	4		5	15

Sep.Grades WKst

Student ID	Last Name	First Name	1.01 Quiz: The Power of Poetry RL.8.1 Points Grade <Numeric MaxPoints:5 Category:Poetry Power>	1.01 Quiz: The Power of Poetry RL.8.1 Scheme Symbol	1.02 Discussion: Form and Meaning RL.8.2 Points Grade <Numeric MaxPoints:20 Category:Unit FM>	1.02 Discussion: Form and Meaning RL.8.2 Scheme Symbol	1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Points Grade <Numeric MaxPoints:30 Category:Unit Misc.>	1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Scheme Symbol	1.05 Quiz: Ravages of War RL.8.5 Points Grade <Numeric MaxPoints:5 Category:Unit Ravages of War>	1.05 Quiz: Ravages of War RL.8.5 Scheme Symbol
123456	Trump	Donald	4	B-			30	A	5	A
321654	Biden	Joe	5	A			30	A	5	A
843921	Norris	Chuck	4	B-			30	A	5	A

 

[C Moore]

Do you know much about Power Query? It makes finagling data pretty easy. Add the power query add-in (it's a Microsoft product) if it's not there. Data tab should look like this:

With a cell selected in the Sep.Grades WKst go to the data tab and click the From Table/Range. Click Advanced editor and paste after #"Changed Type"line the lines following that from here:

let
    Source = Excel.CurrentWorkbook(){[Name="Table_Assignments"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Last Name", type text}, {"First Name", type text}, {"1.01 Quiz: The Power of Poetry RL.8.1 Points Grade <Numeric MaxPoints:5 Category:Poetry Power>", Int64.Type}, {"1.01 Quiz: The Power of Poetry RL.8.1 Scheme Symbol", type text}, {"1.02 Discussion: Form and Meaning RL.8.2 Points Grade <Numeric MaxPoints:20 Category:Unit FM>", type any}, {"1.02 Discussion: Form and Meaning RL.8.2 Scheme Symbol", type any}, {"1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Points Grade <Numeric MaxPoints:30 Category:Unit Misc.>", Int64.Type}, {"1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Scheme Symbol", type text}, {"1.05 Quiz: Ravages of War RL.8.5 Points Grade <Numeric MaxPoints:5 Category:Unit Ravages of War>", Int64.Type}, {"1.05 Quiz: Ravages of War RL.8.5 Scheme Symbol", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type1",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Transposed Table", "Max Points", each Text.BetweenDelimiters([Column1], ":", " ", 1, 0), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Between Delimiters", each ([Max Points] <> "")),
    #"Added Custom Column" = Table.AddColumn(#"Filtered Rows", "Assignment Number", each let splitColumn1 = Splitter.SplitTextByDelimiter(" <", QuoteStyle.None)([Column1]) in Text.Reverse(Text.Middle(Text.Reverse(splitColumn1{0}?), 13, 6)), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom Column",{{"Column1", "Assignment Name"}})
in
    #"Renamed Columns"

It will create another sheet when you hit close and load with a table you can use as a bridge. Should look like this:

 

[jbiehl]

Do you know much about Power Query? It makes finagling data pretty easy. Add the power query add-in (it's a Microsoft product) if it's not there. Data tab should look like this:
View attachment 21620
With a cell selected in the Sep.Grades WKst go to the data tab and click the From Table/Range. Click Advanced editor and paste after #"Changed Type"line the lines following that from here:

let
    Source = Excel.CurrentWorkbook(){[Name="Table_Assignments"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Last Name", type text}, {"First Name", type text}, {"1.01 Quiz: The Power of Poetry RL.8.1 Points Grade <Numeric MaxPoints:5 Category:Poetry Power>", Int64.Type}, {"1.01 Quiz: The Power of Poetry RL.8.1 Scheme Symbol", type text}, {"1.02 Discussion: Form and Meaning RL.8.2 Points Grade <Numeric MaxPoints:20 Category:Unit FM>", type any}, {"1.02 Discussion: Form and Meaning RL.8.2 Scheme Symbol", type any}, {"1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Points Grade <Numeric MaxPoints:30 Category:Unit Misc.>", Int64.Type}, {"1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Scheme Symbol", type text}, {"1.05 Quiz: Ravages of War RL.8.5 Points Grade <Numeric MaxPoints:5 Category:Unit Ravages of War>", Int64.Type}, {"1.05 Quiz: Ravages of War RL.8.5 Scheme Symbol", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type1",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Transposed Table", "Max Points", each Text.BetweenDelimiters([Column1], ":", " ", 1, 0), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Between Delimiters", each ([Max Points] <> "")),
    #"Added Custom Column" = Table.AddColumn(#"Filtered Rows", "Assignment Number", each let splitColumn1 = Splitter.SplitTextByDelimiter(" <", QuoteStyle.None)([Column1]) in Text.Reverse(Text.Middle(Text.Reverse(splitColumn1{0}?), 13, 6)), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom Column",{{"Column1", "Assignment Name"}})
in
    #"Renamed Columns"

It will create another sheet when you hit close and load with a table you can use as a bridge. Should look like this:
View attachment 21622

Thanks! This looks pretty awesome, and I think I could use it in different related task, but in this case, we would need something that wouldn't require the assignments to be typed anywhere.

We won't have assignment names in advance and each teacher is going to have their own workbook. The goal is for them to be able to just copy and paste their grade books (which are exported from another program) into the workbook and have it all roll-up for them, without them doing any work on the back end. It seems futile, but the roll-up worksheet sources a dashboard the includes many other data points to monitor student performance on individual standards.

I should have also mentioned that the codes (RL.8....) are the content standard codes assessed in that assignment, so there will be multiple assignments attached to each standard code and some assignments will have multiple codes. For example, it the 1.01 Quiz might to be linked to RL.8.1, RL.8.2 and RL.8.3, and the 1.02 Quiz might be linked to RL.8.2 and RL.8.3.

Thanks again!

 

[KRice]

Here is one approach. I don't have Excel 365, so XLOOKUP isn't an option for me. Instead, I've formed the necessary array with an INDEX/MATCH construction, coupled with N and IF functions to clear out non-numeric values that lead to errors. Additionally, extracting the max point values is rather messy, so I've added one helper row above the Sep.Grades table and am hopeful that this might still work for you. The sample Sep.Grades worksheet looks like this (note that I added a column L with some 1.06 Quiz values as well as a helper row 1 that holds the formula for extracting the max points):

MrExcel20200827.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1				5		20		30		5		30
2	Student ID	Last Name	First Name	1.01 Quiz: The Power of Poetry RL.8.1 Points Grade <Numeric MaxPoints:5 Category:Poetry Power>	1.01 Quiz: The Power of Poetry RL.8.1 Scheme Symbol	1.02 Discussion: Form and Meaning RL.8.2 Points Grade <Numeric MaxPoints:20 Category:Unit FM>	1.02 Discussion: Form and Meaning RL.8.2 Scheme Symbol	1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Points Grade <Numeric MaxPoints:30 Category:Unit Misc.>	1.03 Graded Assignment: Do Not Go Gentle RL.8.3 Scheme Symbol	1.05 Quiz: Ravages of War RL.8.5 Points Grade <Numeric MaxPoints:5 Category:Unit Ravages of War>	1.05 Quiz: Ravages of War RL.8.5 Scheme Symbol	1.06 Quiz: I don't know what this is called RL.8.6 Points Grade <Numeric MaxPoints:30 Category:Unit Not Knownr>
3	123456	Trump	Donald	4	B-			30	A	5	A	29
4	321654	Biden	Joe	5	A			30	A	5	A	29
5	843921	Norris	Chuck	4	B-			30	A	5	A	15
6
Sep.Grades

Cell Formulas
Range	Formula
D1:L1	D1	=IFERROR(TRIM(SUBSTITUTE(MID(SUBSTITUTE(D$2," ",REPT(" ",99)),MAX(1,FIND("MaxPoints:",SUBSTITUTE(D$2," ",REPT(" ",99)))-50),99),"MaxPoints:",""))*1,"")

The sample Roll-up sheet then identifies the relevant scores (matching row and columns that meet "RL" criteria) and divides them by their corresponding max point values to form an array of scores each having a basis value of 100 %. Unfortunately, I couldn't identify a convenient way to utilize more efficient functions for determining the arithmetic average of the values in this array, so I made use of two SUMPRODUCT functions. The numerator array holds the scores, and those are essentially summed. The denominator array replaces the scores with values of 1, and when they are summed, we obtain a count of the scores. Division then yields an average where each score has equal weight. With 365, you shouldn't have to enter these as array formulas (as 365 will probably treat them as array formulas automatically), but I found it necessary with Excel 2019.

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	75.3%	80.0%	0.0%	100.0%	96.7%
3	321654	Biden	Joe	79.3%	100.0%	0.0%	100.0%	96.7%
4	843921	Norris	Chuck	66.0%	80.0%	0.0%	100.0%	50.0%
5
Roll-up

Cell Formulas
Range	Formula
D2:H4	D2	=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),{1},"")),MATCH($A2,Sep.Grades!$A$3:$A$5,0),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KRice]

After posting, I noticed a problem with my suggestion above...
ISNUMBER(Sep.Grades!$D$3:$L$5/Sep.Grades!$D$1:$L$1)
this part of the denominator treats a blank as 0, which is a number, so that leads to an overcount of the number of scores. To address this, I've added a more convoluted check to confirm that the ratio is a number and that it is not 0. Others might have a better way to approach this.

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%
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)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[C Moore]

Nothing was typed, it was all pulled in from the pasted data. I selected the assignment number because it was easy to compare against for your averaging formulas. It could be just as easily the text between ":" and RL. I did nothing but run the query based on the table that was 'imported.' It's like a little program, paste and refresh.

let
    Source = Excel.CurrentWorkbook(){[Name="Table_Assignments"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Kept First Rows" = Table.FirstN(#"Demoted Headers",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Transposed Table", "Max Points", each Text.BetweenDelimiters([Column1], ":", " ", 1, 0), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Between Delimiters", each ([Max Points] <> "")),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Filtered Rows", "Assignment Title", each Text.BetweenDelimiters([Column1], " ", " RL", 1, 0), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Column1", "Assignment Details"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Max Points", type number}})
in
    #"Changed Type"

 

[jbiehl]

Nothing was typed, it was all pulled in from the pasted data. I selected the assignment number because it was easy to compare against for your averaging formulas. It could be just as easily the text between ":" and RL. I did nothing but run the query based on the table that was 'imported.' It's like a little program, paste and refresh.
View attachment 21961

let
    Source = Excel.CurrentWorkbook(){[Name="Table_Assignments"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Kept First Rows" = Table.FirstN(#"Demoted Headers",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Transposed Table", "Max Points", each Text.BetweenDelimiters([Column1], ":", " ", 1, 0), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Text Between Delimiters", each ([Max Points] <> "")),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Filtered Rows", "Assignment Title", each Text.BetweenDelimiters([Column1], " ", " RL", 1, 0), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Column1", "Assignment Details"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Max Points", type number}})
in
    #"Changed Type"

Thanks for the explanation!

 

[jbiehl]

After posting, I noticed a problem with my suggestion above...
ISNUMBER(Sep.Grades!$D$3:$L$5/Sep.Grades!$D$1:$L$1)
this part of the denominator treats a blank as 0, which is a number, so that leads to an overcount of the number of scores. To address this, I've added a more convoluted check to confirm that the ratio is a number and that it is not 0. Others might have a better way to approach this.

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%
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)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thanks for the detailed process!
I did end up converting this to XLOOKUP, like you said, (a process that probably should have taken 5 minutes, but took me hours - lol). I'm using this, but I'm still going to try to find a way to work the helper row formula into the actual formula so the teachers can copy and paste their entire grade book using the select all button on the top left instead of starting on row 2. I know it seems like a little thing, but I think it will be a big deal for some of them.