A bookstore would like to determine what kinds of books were sold on a particular day. Data has been downloaded into a spreadsheet shown in the worksheet bookstore sales. The downloaded data may contain leading or trailing spaces due to a defect in the download program. In this problem we define the syntax of the book information using the widely used EBNF notation.

Each book is described by the following syntax rule: BookTitle "by" AuthorName ["," FormatCode] BookTitle is not allowed to contain by. AuthorName is not allowed to contain a comma. The square brackets indicate that FormatCode is an optional field. The presence of a comma after the AuthorName indicates that the FormatCode is in the input. The absence of a comma after the AuthorName means that the FormatCode is absent.

1. Transform the data in Column A into an Excel Table, rename it BookData.

2. Create an Excel Table named BCODES for the data below to find the Type given the format code : Format Code Type HC Hardcover PB Paperback EB Ebook AB Audio Book

3. In column C, extract the title of each book.

4. In column D, extract the author of each book

5. In column E, extract the format code of each book. Be sure to handle codes with leading and trailing spaces.

6. In column F, insert the type associated with the books format code. Format code is an optional field, which means the format code may be missing. If the field is missing, keep the cell empty for the Format Code column and put the value UNKNOWN in the Format column.

7. Generate headers for columns G through J using the Format Code column in the BCODES table. Use TRANSPOSE, and SORT to create the headers from the BCODES table as well as a LOOKUP function to retrieve the book type values from BCODES.

8. Create one formula to count (using COUNTIFS) the data for each book format in columns G through J.

9. Find the percentage of each format sold with respect to all formats (except UNKNOWN). Store the percentage at the bottom of each column (G:J). Use one dynamic formula that is NOT duplicated.

10. Format your solution as shown in Figure 3.