The Lillydata.xlsx file contains information from a market-research survey that was used to gather insights to aid in designing a new blood-pressure drug. Fifteen experts (six from Eli Lilly and Company and nine from other firms—see column N) were asked to compare five sets of four potential Lilly products. The fifth choice in each scenario is that competitor’s drug is chosen over the four listed Lilly drugs. For example, in the first scenario, the second option considered would be a Lilly drug that reduced blood pressure 18 points, resulted in 14 percent of side effects, and sold for $16. The range I6:N21 contains the choices each expert made for each of the five scenarios. For example, the first expert (who worked for Lilly) chose a competitor’s drug when faced with Scenario 1 and chose the first listed drug when faced with Scenario 2.

Use this information to do the following:

Enter a formula that can be copied from I2 to I2:M5 that calculates the price for each scenario and option in I2:M5. 

Enter an array formula in I23 that can be copied to I23:I32 and then to J23:M32 that calculates for each question the frequency of each response (1–5), broken down by Lilly and non-Lilly experts. Thus, for Question 1, one Lilly expert responded 1, three responded 2, and two responded 5.