From the table below, I wanted to calculate the average percentage of training. If the status starts with "3" show as 100, "2" show as 50, and "3" show as 0 and then calculate the average. The summary table doesn't include the subjects which are blank (highlighted in grey)
I am able to get the status calculation using the formula "=LET(_classes,FILTER('Content Tracker'!A3:A1048576,'Content Tracker'!A3:A1048576<>""), _subjects,FILTER('Content Tracker'!B3:B1048576,'Content Tracker'!B3:B1048576<>""), _trainings,FILTER('Content Tracker'!E3:G1048576,'Content Tracker'!B3:B1048576<>""), _merge,HSTACK(SCAN(_subjects,_subjects,LAMBDA(x,y,IF(y="",x,y))),_trainings), _uclasses,UNIQUE(INDEX(_merge,,1)), _trainingonecomp,MAP(_uclasses,LAMBDA(x,SUM(--(x=INDEX(_merge,,1))*(INDEX(_merge,,2)<>0)IF(ISNUMBER(SEARCH("3",INDEX(_merge,,2))),100,IF(ISNUMBER(SEARCH("2",INDEX(_merge,,2))),50,0))))), _trainingtwocomp,MAP(_uclasses,LAMBDA(x,SUM(--(x=INDEX(_merge,,1))(INDEX(_merge,,3)<>0)IF(ISNUMBER(SEARCH("3",INDEX(_merge,,3))),100,IF(ISNUMBER(SEARCH("2",INDEX(_merge,,3))),50,0))))), _trainingthreecomp,MAP(_uclasses,LAMBDA(x,SUM(--(x=INDEX(_merge,,1))(INDEX(_merge,,4)<>0)*IF(ISNUMBER(SEARCH("3",INDEX(_merge,,4))),100,IF(ISNUMBER(SEARCH("2",INDEX(_merge,,2))),50,0))))), _avg,AVERAGE(_trainingonecomp,_trainingtwocomp,_trainingthreecomp), _output,VSTACK(HSTACK("classes","subjects","Training 1 Completed","Training 2 Completed","Training 3 Completed","Average"),HSTACK(_classes,_subjects,_trainingonecomp,_trainingtwocomp,_trainingthreecomp,_avg)), _output)"
This is the output I got:
I am not able to remove the subjects (highlighted in yellow) with blank values in the source files, and the class and average columns throw some errors.
This is the result expected:
Can anyone assist me with this to achieve the expected result?
