Figure $2$: Example for Problem $3$

Then using the grade point values in Table $1$b$,$ use one spilling SWITCH$()$ formula and map the grades to a point code.

Tally the number of A$\text{'}$s$,$ B$\text{'}$s$,$ etc. earned. Use the SORT$()$ and UNIQUE$()$ function to create this tally as shown in the fiqure $2.$

You may find Excel is filling in formula down columns for you. If this interferes with your work, you can turn it off by going to the File tab. Select Options. Then select Proofing, then AutoCorrect Options, then AutoFormat As You Type. Uncheck Fill formulas in tables to create calculated columns. Then click OK$.$

Problem $4$ Audit Planning $(30$ points$)$

You are given a list of QXZ audit clients. You are told if the firm is larrye or small and if they passed or failed an audit. The information defines when the next audit will be$.$ The rules are:

If a firm is large and failed, their next audit is in two months.

If a firm is large and passed, their next audit is in three months.

If a firm is small, their next audit is in four months.

Using the worksheet Audit, transform the data from column D to column G to an Excel Table and name it TallyData.

Write a function using

LAMBDA$($last$\_$audit,company$\_$size,audit$\_$result,$\_$LET$(...)),$

where last$\_$audit is a column of dates, company$\_$size is a column of either "large" or "small" and result is a column of either "pass" or "fail". Name the function NextAudit$\_$CSA and call it on the given list of QXZ audit clients. NextAudit$\_$CSA will correctly implement the three rules spelled out above.

$5$