// Numbas version: finer_feedback_settings
{"name": "EXCEL - Bubble Shooter scores ", "extensions": ["stats", "sheets", "random_person", "download-text-file"], "custom_part_types": [], "resources": [], "navigation": {"allowregen": true, "showfrontpage": false, "preventleave": false, "typeendtoleave": false}, "question_groups": [{"pickingStrategy": "all-ordered", "questions": [{"name": "EXCEL - Bubble Shooter scores ", "tags": [], "metadata": {"description": "<p>Find the highest scoring people over a course of days using the MAX, MATCH and INDEX functions</p>", "licence": "Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International"}, "statement": "<p>{n} people have been playing Bubble Shooter over {days} days. Please answer the questions below about the points they scored.</p>\n<p>{download_link(\"peoples_scores.csv\",csv1)}</p>\n<p><span class=\"ui-provider cbm cbn c d e f g h i j k l m n o p q r s t cbo cbp w x y z ab ac ae af ag ah ai aj ak\" dir=\"ltr\"><strong>WARNING</strong> The data supplied for this question changes each time. If you retry this question you will need to redownload the csv.</span></p>", "advice": "<p>a) The answer is <strong>{ansa}</strong>.</p>\n<p>The idea is to first find the <code>MAX</code> score on day {d1}.&nbsp;When we've got that we can use <code>MATCH</code> and <code>INDEX</code> to find which person has that maximum.</p>\n<p>In the cell {working_col}2&nbsp;add the formula <code>=MAX({maxcol}2:{maxcol}{n+1})</code>.&nbsp;</p>\n<p>We can now use <code>INDEX</code> and <code>MATCH</code> to find the name.</p>\n<p><code>=INDEX(A2:A{n+1},MATCH({working_col}2,{maxcol}2:{maxcol}{n+1},0))</code></p>\n<p></p>\n<p>Alternatively, you can use <code>MATCH</code> first to find the row which has the maximum score, and then use <code>INDEX</code> to find the name corresponding to that row. This will give the same result but avoids using one function inside another.</p>\n<p></p>\n<p>b) The answer is <strong>{ansb}</strong>.&nbsp;</p>\n<p>To find the answer, we follow the same process as in part e but using the <code>MIN</code> function instead.&nbsp;</p>\n<p></p>\n<p>c) The answer is day <strong>{ansc}</strong>.</p>\n<p>The idea is to find the <code>MAX</code> score for {person1}. When we've got that we can use <code>MATCH</code> and <code>INDEX</code> to find which day has that maximum.</p>\n<p>In the cell {working_col}{p1+2} add the formula <code>=MAX(B{p1+2}:{maxcol}{p1+2})</code>.</p>\n<p>We can now use <code>INDEX</code> and <code>MATCH</code> to find the day.</p>\n<p><code>=INDEX(B1:{end_col}1,MATCH({working_col}{p1},B{p1}:{maxcol}{p1},0)).</code></p>\n<p></p>\n<p>d) The answer is day <strong>{ansd}</strong>.</p>\n<p>To find the answer, we follow the same process as in part g but using the <code>MIN</code> function instead.&nbsp;</p>\n<p></p>\n<p>e) The answer is <strong>{anse} points</strong>.&nbsp;</p>\n<p>To find this we need to calculate the mean score in the row that corresponds to {person3}.&nbsp;</p>\n<p>In cell, {working_col}{p3+2}, add the formula <code>=AVERAGE(B{p3+2}:{end_col}{p3+2})</code>.</p>\n<p></p>\n<p>f) The answer is <strong>{ansf} points</strong>.&nbsp;</p>\n<p>To find this we need to calculate the mean on day {d4}.&nbsp;</p>\n<p>In the cell {meancol}{n+2}, add the formula <code>=AVERAGE({meancol}2:{meancol}{n+1})</code>.</p>\n<p></p>\n<p>g) The answer is <strong>{ansg} points</strong>.&nbsp;</p>\n<p>To find this, we need to calculate the mean of the whole dataset.&nbsp;</p>\n<p>In the cell {end_col}{n+2}, add the formula <code>=AVERAGE(B2:{end_col}{n+1})</code>.</p>\n<p></p>", "rulesets": {}, "extensions": ["download-text-file", "random_person", "sheets", "stats"], "builtin_constants": {"e": true, "pi,\u03c0": true, "i": true}, "constants": [], "variables": {"people": {"name": "people", "group": "Ungrouped variables", "definition": "random_people(n)", "description": "", "templateType": "anything", "can_override": false}, "names": {"name": "names", "group": "Ungrouped variables", "definition": "map(x['name'],x,people)", "description": "", "templateType": "anything", "can_override": false}, "data": {"name": "data", "group": "Ungrouped variables", "definition": "makeq(names, days)", "description": "", "templateType": "anything", "can_override": false}, "csv": {"name": "csv", "group": "Ungrouped variables", "definition": "csv(data[0])", "description": "", "templateType": "anything", "can_override": false}, "n": {"name": "n", "group": "Ungrouped variables", "definition": "random(50..80)", "description": "", "templateType": "anything", "can_override": false}, "days": {"name": "days", "group": "Ungrouped variables", "definition": "random(14..28)", "description": "", "templateType": "anything", "can_override": false}, "d1": {"name": "d1", "group": "Ungrouped variables", "definition": "random(1..days)", "description": "", "templateType": "anything", "can_override": false}, "d2": {"name": "d2", "group": "Ungrouped variables", "definition": "random(1..days)", "description": "", "templateType": "anything", "can_override": false}, "person1": {"name": "person1", "group": "Ungrouped variables", "definition": "names[p1]", "description": "", "templateType": "anything", "can_override": false}, "person2": {"name": "person2", "group": "Ungrouped variables", "definition": "names[p2]", "description": "", "templateType": "anything", "can_override": false}, "person3": {"name": "person3", "group": "Ungrouped variables", "definition": "names[p3]", "description": "", "templateType": "anything", "can_override": false}, "d3": {"name": "d3", "group": "Ungrouped variables", "definition": "random(1..days)", "description": "", "templateType": "anything", "can_override": false}, "p1": {"name": "p1", "group": "Ungrouped variables", "definition": "random(0..n-1)", "description": "", "templateType": "anything", "can_override": false}, "p2": {"name": "p2", "group": "Ungrouped variables", "definition": "random(0..n-1)", "description": "", "templateType": "anything", "can_override": false}, "p3": {"name": "p3", "group": "Ungrouped variables", "definition": "random(0..n-1)", "description": "", "templateType": "anything", "can_override": false}, "cond1": {"name": "cond1", "group": "Ungrouped variables", "definition": "sum(map(if(max(rowdays[d1-1])=rowdays[d1-1][x],1,0),x,0..n) )", "description": "", "templateType": "anything", "can_override": false}, "ansb": {"name": "ansb", "group": "Ungrouped variables", "definition": "names[sum(map(if(min(rowdays[d2-1])=rowdays[d2-1][x],x,0),x,0..n))]", "description": "", "templateType": "anything", "can_override": false}, "ansc": {"name": "ansc", "group": "Ungrouped variables", "definition": "sum(map(if(max(rowpeople[p1])=rowpeople[p1][x],x,0),x,0..n))+1", "description": "", "templateType": "anything", "can_override": false}, "ansd": {"name": "ansd", "group": "Ungrouped variables", "definition": "sum(map(if(min(rowpeople[p2])=rowpeople[p2][x],x,0),x,0..n))+1", "description": "", "templateType": "anything", "can_override": false}, "anse": {"name": "anse", "group": "Ungrouped variables", "definition": "precround(mean(rowpeople[p3]),2)", "description": "", "templateType": "anything", "can_override": false}, "rowdays": {"name": "rowdays", "group": "Ungrouped variables", "definition": "transpose(rowpeople)", "description": "", "templateType": "anything", "can_override": false}, "rowpeople": {"name": "rowpeople", "group": "Ungrouped variables", "definition": "matrix(testing)", "description": "", "templateType": "anything", "can_override": false}, "ansa": {"name": "ansa", "group": "Ungrouped variables", "definition": "names[sum(map(if(max(rowdays[d1-1])=rowdays[d1-1][x],x,0),x,0..n))]", "description": "", "templateType": "anything", "can_override": false}, "cond2": {"name": "cond2", "group": "Ungrouped variables", "definition": "sum(map(if(min(rowdays[d2-1])=rowdays[d2-1][x],1,0),x,0..n) )", "description": "", "templateType": "anything", "can_override": false}, "testing": {"name": "testing", "group": "Ungrouped variables", "definition": "matrix(map(data[0][x][1..days+1],x,1..n))", "description": "", "templateType": "anything", "can_override": false}, "d4": {"name": "d4", "group": "Ungrouped variables", "definition": "random(1..days)", "description": "", "templateType": "anything", "can_override": false}, "ansf": {"name": "ansf", "group": "Ungrouped variables", "definition": "precround(mean(rowdays[d4-1]),2)", "description": "", "templateType": "anything", "can_override": false}, "ansg": {"name": "ansg", "group": "Ungrouped variables", "definition": "precround({sum_cells(rowdays)/total},2)", "description": "", "templateType": "anything", "can_override": false}, "total": {"name": "total", "group": "Ungrouped variables", "definition": "n*days", "description": "", "templateType": "anything", "can_override": false}, "end_col": {"name": "end_col", "group": "Ungrouped variables", "definition": "upper(letterordinal(days))", "description": "", "templateType": "anything", "can_override": false}, "working_col": {"name": "working_col", "group": "Ungrouped variables", "definition": "upper(letterordinal(days+2))", "description": "", "templateType": "anything", "can_override": false}, "maxcol": {"name": "maxcol", "group": "Ungrouped variables", "definition": "upper(letterordinal(d1))", "description": "", "templateType": "anything", "can_override": false}, "meancol": {"name": "meancol", "group": "Ungrouped variables", "definition": "upper(letterordinal(d4))", "description": "", "templateType": "anything", "can_override": false}, "csv1": {"name": "csv1", "group": "Ungrouped variables", "definition": "csv(map(data[0][x][0..days+1],x,1..n), nameday)", "description": "", "templateType": "anything", "can_override": false}, "day": {"name": "day", "group": "Ungrouped variables", "definition": "map(\"Day \" + x, x,1..days)", "description": "", "templateType": "anything", "can_override": false}, "nameday": {"name": "nameday", "group": "Ungrouped variables", "definition": "[\"name\"] + day", "description": "", "templateType": "anything", "can_override": false}}, "variablesTest": {"condition": "(d1<>d2<>d3)and(cond1=1)and(cond2=1)", "maxRuns": 100}, "ungrouped_variables": ["people", "names", "data", "csv", "n", "days", "d1", "d2", "d3", "person1", "person2", "person3", "p1", "p2", "p3", "cond1", "cond2", "ansa", "ansb", "ansc", "ansd", "anse", "rowdays", "rowpeople", "testing", "d4", "ansf", "ansg", "total", "end_col", "working_col", "maxcol", "meancol", "csv1", "day", "nameday"], "variable_groups": [], "functions": {"makeQ": {"parameters": [["names", "list"], ["days", "number"]], "type": "anything", "language": "javascript", "definition": "let name_length = names.length;\nvar arr = [['name']];\nfor (let i = 0; i < days; i++) {\n  arr[0].push(i+1);\n}\nvar line = [];\nfor (let i = 0; i < name_length; i++) {\n  line.push(names[i])\n  for (let j = 0; j < days; j++) {\n     line.push(Math.floor(Math.random()*980));\n  }\n  arr.push(line);\n  line = [];\n}\nlet max = Math.floor(Math.random()*19)+980;\nlet name_index = Math.floor(Math.random()*names.length);\nlet name = names[name_index];\nlet rand_day_index = Math.floor(Math.random()*days);\narr[name_index+1][rand_day_index+1] = max;\n\n\nreturn [arr,name]"}}, "preamble": {"js": "", "css": ""}, "parts": [{"type": "information", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>Find the person with the highest score on day {d1}.&nbsp;</p>\n<p><em>Enter the name of the person with the highest score.</em></p>\n<p>[[0]]</p>\n<p></p>\n<details>\n<summary><span style=\"text-decoration: underline;\">Help I don't know where to start!</span></summary>\nYou'll need to use the <code>MATCH</code> and <code>INDEX</code> functions.</details>", "gaps": [{"type": "patternmatch", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansA}", "displayAnswer": "ans", "matchMode": "exact"}], "sortAnswers": false}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>Find the person with the lowest score on day {d2}.</p>\n<p><em>Enter the name of the person with the lowest score.</em></p>\n<p>[[0]]</p>", "gaps": [{"type": "patternmatch", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansB}", "displayAnswer": "", "matchMode": "exact"}], "sortAnswers": false}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>Find the day when {person1} scored the most points.</p>\n<p><em>Enter the day as a number (e.g., if {person1} achieved the higherst score on the 3rd day, type 3).</em></p>\n<p>[[0]]</p>", "gaps": [{"type": "jme", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansC}", "showPreview": true, "checkingType": "absdiff", "checkingAccuracy": 0.001, "failureRate": 1, "vsetRangePoints": 5, "vsetRange": [0, 1], "checkVariableNames": false, "singleLetterVariables": false, "allowUnknownFunctions": true, "implicitFunctionComposition": false, "caseSensitive": false, "valuegenerators": []}], "sortAnswers": false}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>Find the day when {person2} scored the lowest points.</p>\n<p><em>Enter the day as a number (e.g., if {person2} achieved the lowest score on the 4th day, type 4).</em></p>\n<p>[[0]]</p>", "gaps": [{"type": "jme", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansD}", "showPreview": true, "checkingType": "absdiff", "checkingAccuracy": 0.001, "failureRate": 1, "vsetRangePoints": 5, "vsetRange": [0, 1], "checkVariableNames": false, "singleLetterVariables": false, "allowUnknownFunctions": true, "implicitFunctionComposition": false, "caseSensitive": false, "valuegenerators": []}], "sortAnswers": false}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>Find the mean score for {person3} over the {days} days.</p>\n<p><em>Enter the mean score below. If needed, round your answer to 2 decimal places.</em></p>\n<p>[[0]] points.</p>", "gaps": [{"type": "jme", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansE}", "showPreview": true, "checkingType": "absdiff", "checkingAccuracy": 0.001, "failureRate": 1, "vsetRangePoints": 5, "vsetRange": [0, 1], "checkVariableNames": false, "singleLetterVariables": false, "allowUnknownFunctions": true, "implicitFunctionComposition": false, "caseSensitive": false, "valuegenerators": []}], "sortAnswers": false}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>On average, how many points did a player score on day {d4}?</p>\n<p><em>Enter your answer below. If needed, round your answer to 2 decimal places.</em></p>\n<p><em>[[0]] points.</em></p>\n<p><em></em></p>", "gaps": [{"type": "jme", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansF}", "showPreview": true, "checkingType": "absdiff", "checkingAccuracy": 0.001, "failureRate": 1, "vsetRangePoints": 5, "vsetRange": [0, 1], "checkVariableNames": false, "singleLetterVariables": false, "allowUnknownFunctions": true, "implicitFunctionComposition": false, "caseSensitive": false, "valuegenerators": []}], "sortAnswers": false}, {"type": "gapfill", "useCustomName": false, "customName": "", "marks": 0, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "prompt": "<p>On average, how many points did a player score in a day?</p>\n<p><em>Enter your answer below. If needed, round your answer to 2 decimal places.</em></p>\n<p>[[0]] points.</p>\n<details>\n<summary><span style=\"text-decoration: underline;\">I need a hint!</span></summary>\nThis involves calculating the mean of the whole dataset.</details>", "gaps": [{"type": "jme", "useCustomName": false, "customName": "", "marks": 1, "scripts": {}, "customMarkingAlgorithm": "", "extendBaseMarkingAlgorithm": true, "unitTests": [], "showCorrectAnswer": true, "showFeedbackIcon": true, "variableReplacements": [], "variableReplacementStrategy": "originalfirst", "nextParts": [], "suggestGoingBack": false, "adaptiveMarkingPenalty": 0, "exploreObjective": null, "answer": "{ansg}", "showPreview": true, "checkingType": "absdiff", "checkingAccuracy": 0.001, "failureRate": 1, "vsetRangePoints": 5, "vsetRange": [0, 1], "checkVariableNames": false, "singleLetterVariables": false, "allowUnknownFunctions": true, "implicitFunctionComposition": false, "caseSensitive": false, "valuegenerators": []}], "sortAnswers": false}], "partsMode": "all", "maxMarks": 0, "objectives": [], "penalties": [], "objectiveVisibility": "always", "penaltyVisibility": "always", "type": "question", "contributors": [{"name": "Ed Southwood", "profile_url": "https://numbas.mathcentre.ac.uk/accounts/profile/2415/"}, {"name": "Ben McGovern", "profile_url": "https://numbas.mathcentre.ac.uk/accounts/profile/4872/"}, {"name": "Evi Papadaki", "profile_url": "https://numbas.mathcentre.ac.uk/accounts/profile/18113/"}], "resources": []}]}], "contributors": [{"name": "Ed Southwood", "profile_url": "https://numbas.mathcentre.ac.uk/accounts/profile/2415/"}, {"name": "Ben McGovern", "profile_url": "https://numbas.mathcentre.ac.uk/accounts/profile/4872/"}, {"name": "Evi Papadaki", "profile_url": "https://numbas.mathcentre.ac.uk/accounts/profile/18113/"}]}