Question

Use the SUMIF function in cell J10 to determine the total number of free throws made by students with black hair.

Use the SUMIF function in cell J17 to determine the total number of free throws made by students with brown eyes.

Use the AVERAGEIF function in cell K10 to determine the average number of free throws made by students with black hair.

Use the AVERAGEIF function in cell K17 to determine the average number of free throws made by students with brown eyes.

Students with "Red" hair had the highest average number of free throws made on the "Hair Color Summary" table and students with "Hazel" eyes had the highest average number of free throws made on the "Eye Color Summary" table. Use the OR function in cell F9 to determine if Student 1 gets a t-shirt. Students get a t-shirt if they have either "Red" hair or "Hazel" eyes. Design your function to return TRUE if an individual student gets a t-shirt and FALSE if they do not.

Use an appropriate function or formula in cell K22 to calculate the number of t-shirts that will be given away.

Answer 1

i. In cell J10, enter the formula -SUMIF(c9:c31,"Black, E9 E31)". The below screenshot shows the formula, Student Hair Color Get Shirt Hair Color Summa Count SUMIF(c9c, Black" E9 E31 12 Brown Brown Black Brown Red Blond 12 Black Brown Black Blue Eve Color Summa Count 16 Black Red Brown Blue Brown Blue Hazel Green 17 10 Brown Green How many students will get a shirt? Blue Brown 25 Blond Blue 21 Brown Blond Brown Blue 30 ii.Then press enter

Student Hair Color Eve Color Free Throws Get Shirt? Hair Color Summary Count Green Brown Avera Black Bromn Red Blond 42 12 SUMIF(C9 C31,"Red E9 E31 14 15 Blue Blue Blue Black Eye Color Summary Count 10 10 Avera Brown Blue 17 18 19 Brown Blue Hazel Green 21 13 Green How many students will get a shirt? Black 1S 19 26 Blond Blue 31 Blond Blue v. In cell J13, enter the formula -SUMIF(C9:C31, Blond", E9.E31). The below screenshot shows the formula,

U16 Student Hair Color Eve Color Free Throws Get Shirt? Hair Color S Count Avera Black Brown Brown Blue Blue Blond 12 15 16 17 Eve Color Summary Count Blue Brown 10 Red Blue 19 Green How many students will get a shirt? Black 16 Black 25 26 Blue Blond 31 Blond Blue Step 4: Use AVERAGEIF in cell K10 In cell K 10, enter the formula"-AVERAGEF(c9c3 1·"Black'. E9:E31)". The below screenshot shows the fomula,

vi. The result after the above formulas implemented is as follows, K10 AVERAGEIF(C9:C31, Black",E9:E31) Student Hair Color Eve Color Free Throws Get Shirt? Hair Color Summary Count 5.25 5.83 Brown Black Brown Red Blond 12 Brown Blue Eve Color Summary Count 16 Brown Black Avera Brown Blue 20 12 Brown How many students will get a shirt? Brown Black m Hazel 25 17 Brown 29 21 23 Step 5: Use AVERAGEIF in cell K17: i. In cell K17, enter the formulaAVERAGEIF(D9 D31, "Black", E9 E31). The below screenshot shows the formula

K17 AVERAGEIF(D9:D31,"Brown" E9:E31) Hair Color Summa Count Student Hair Color Eve Color Free Throws Get Shirt? Avera 5 25 5.83 Black Red Blond Blue Blue 13 Eye Color Summa Count 15 16 Sum Brown Blue Red 19 Green How many students will get a shirt? Hazel Black 1S 19 Blue Blue Blue 26 30 Blond

ii.Now select cell F9 and drag to till F31 OR(C9-"Red",D9-"Hazel") Student Hair Color Eve Color Free Throws Get Shirt? Hair Color Summary Brown Green FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Count Sum Avera 5.25 5.83 Black Bronn Red Blond Blue Eve Color Summa Sum 16 17 18 Count 10 Arera Brown Blue Red 5.3 Green 21 13 10 Brown How many students will get a shirt? 23 Brown Hazel 26 Blond Blue Step 7: Using appropriate function in cell K22