Fix the following errors in the Guest List worksheet. Use cell references and named ranges where appropriate. All the named ranges you might need have been defined for you. Refer to the Named Ranges Reference sheet for a description of each defined name. a$.$ In cell K$13,$ the formula to create the GuestID should use the first two characters from cell B$13$ combined with the first three characters from cell E$13.$ Fix the formula to have the result be Ci$417.$ Copy the formula to the remaining cells in the column.

b$.$ In cell E$3,$ the formula to calculate the number of tables should result in a whole number only. Be sure to round the number up so you aren$$t left with a guess without a seat.

c$.$ The formula in cell E$4$ should calculate the total number of guests from the city listed in cell D$4.$ d$.$ The formula in cell E$5$ should calculate the number of single guests attending from the city listed in cell D$5.$

e$.$ The formula in cell E$6$ should calculate the average number of guests attending from the city listed in cell D$6.$ The formula should also round up to a whole number.

f$.$ In cell E$7,$ use a database function to calculate the number of guests from the city of Auburn with a last name beginning with the letter J$.$ You will need to correct issues in both the formula and in the criteria data. $($Hints: DSUM is not the appropriate database function to use in this example. The Database arguments should use a named range, but not GuestList. Be sure to set the Criteria$\_$range argument to include all the column headings so you can change the question and criteria later without having to change the formula. And remember, you are looking for last names that begin with the letter J$.$ There$$s no problem with using Last Name as the Field criteria.$)$

$5.$ Fix the following errors in the Shopping List worksheet. Use cell references and named ranges where appropriate. All the named ranges you might need have been defined for you. Refer to the Named Ranges Reference sheet for a description of each defined name.

a$.$ The formula in cell B$2$ should calculate the total cost of all the shopping items by multiplying the number of items ordered in the Quantity column by the cost of each item in the TotalCostperUnit column and then summing the values. Be sure to round the final value to two decimal places. If necessary, apply the Accounting Number Format.

b$.$ The formula in cell B$4$ should allow you to look up the vendor for the item listed in cell B$3.$ Using a nested INDEX and MATCH formula is the right idea, but the formula is not quite right. Add an IFERROR function to display item not found if the formula results in an error. Also, use absolute references so you can copy the formula to cells B$5$ and B$6.$ c$.$ In cell B$5,$ copy and paste the corrected nested INDEX and MATCH formula and then update the Column$\_$num argument to find the quantity ordered.

d$.$ In cell B$6,$ copy and paste the corrected nested INDEX and MATCH formula and then update the Column$\_$num argument to find the item$$s total cost per unit. Edit the formula to calculate the total cost by multiplying the result of the INDEX formula by the quantity ordered $($cell B$5).($Hint: Maintain the IFERROR function as the outermost function in the nested formula.$)$ If necessary, apply the Accounting Number Format. e$.$ There are three items in the shopping list that are based on the number of guests, but the quantity ordered should be rounded up to the nearest whole number. Add the appropriate rounding function to the formulas in cells D$10,$ D$11,$ and D$23.$

f$.$ The formula in cell E$4$ should calculate how many months it will take to pay off the loan using the total cost in cell B$2$ and the loan terms in cells E$2$ and E$3.$ The formula is using the wrong function and the arguments are in the wrong order. Once you correct this formula, the final cost of the party $($cell E$5)$ will be correct as well.

$6.$ Once the guest list analysis and party expenses have been corrected, you can determine whether or not the party will meet the fundraising goal. Go to the Fundraising Goal worksheet. The IF formula in cell B$10$ evaluates whether or not the fundraising goal was met. However, if the goal was not met, the party organizers also want to know if they met at least $75\%$ of the goal.

a$.$ The formula in cell B$10$ should display Goal met! if the party income $($donations$,$ cell B$6,$ minus expenses, cell B$8)$ is greater than or equal to the fundraising goal $($cell B$2).$ If the income is within $75\%$ of the goal $($greater than or equal to B$2*\mathrm{.}75),$ then the formula should display this message: within $25\%$ of goal. If the income is less than $75\%$ of the fundraising goal, then the formula should display this message: less than $75\%$ of goal.