Table $1$: Health club plans

Plan Name Code Monthly Price

Platinum P $$99$

Gold G $$75$

Silver S $$50$

No Frills N $$20$

Table $2$: Pre$-$payment discounts

Number of Days in Advance Discount

$<70\%$

$>=7$ and $<135\%$

$>=13$ and $<2910\%$

$>=2915\%$

$.$

$9.$ Using IF$(),$ filter the data for each plan. Use XLOOKUP$()$ to find the plan$$s name based on the

plan code and then match against the header using IF$().$ Write a function called TwoDimFilter with

two arguments having different directions. Use the LAMBDA$($codes$,$ headers,...$),$ where codes is a

column of codes $($the plan codes$)$ and headers is a row of legal names $($the legal plan names$).$ The

output is a column of $1$ and $"".$ Note that function TwoDimFilter is similar to a function you wrote

in the previous assignment.

$10.$ Calculate the total number of customers in each plan. Calculate the percentages as well.

$11.$ Use conditional formatting to highlight any plan that was wrongly coded. $[$Total due $=$ $$0]$

$12.$ Format the worksheet as shown in Figure $1.$

$13.$ Produce the following tables from the CustomersT table using the FILTER function. FILTER is

in the category of lookup and reference functions. Filtering is a widely used operation both in

spreadsheets and databases.

$($a$)$ Produce a table with all Platinum customers.

$($b$)$ Produce a table with all customers who paid before June $1,2019.$

$($c$)$ Produce a table with all Platinum or Gold customers.

$($d$)$ Produce a table with all Platinum or Gold customers who paid after $6/15/2019.$ The condition

is of the form:

AND$($OR $($plan$\_$code$="$P$",$ plan$\_$code$="$G$"),$paid$\_$date$>$DATE$(2019,6,15)).$

You must find a solution so the condition can be expressed in this natural form inside a

LAMBDA. Using a LAMBDA that takes a filtercondition function as an argument will help

you to achieve this goal. The LAMBDA looks like LAMBDA$($CustomerTable$,$ filter$\_$condition,

LET$(...))($CustomerT$,$ LAMBDA$(...)).$ The second LAMBDA is the actual filter condition like

the one above wrapped in a LAMBDA