You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad decreases as shown in the following table:

Number of Ads PurchasedCost per Ad1 - 5$ 120006 - 10$1100011 - 20$1000021 or higher$9000

For example, if you buy 8 ads, you pay $11,000 per ad, hence you pay $88000 total. If you buy 14 ads, you pay $10,000 for each ad, hence you pay $140000 total. Design and write formula that yields the total cost of purchasing any number of ads.

Question 1: Which of the following is the correct IF statement to solve the above problem?

1) =IF([no. of ads.]<=5, [no.of ads.] *12000,IF( [no.of ads.] <=10, [no.of ads.] *11000,IF( [no.of ads.] >20, [no.of ads.] *9000, [no.of ads.] *10000)))

2)=IF( [no.of ads.] <=5, [no.of ads.] *12000,IF( [no.of ads.] <=10, [no.of ads.] *11000,IF(C5<=20, [no.of ads.] *10000,IF( [no.of ads.] >20, [no.of ads.] *9000)))

3)=IF( [no.of ads.] <=5, [no.of ads.] ,IF( [no.of ads.] <=10, [no.of ads.] ,IF( [no.of ads.] >20, [no.of ads.] , [no.of ads.] )))

4)=IF([no.of ads.]>5,[no.of ads.]*9000,IF([no.of ads.]>10,[no.of ads.]*10000,IF([no.of ads.]<20,[no.of ads.]*11000,[no.of ads.]*12000)))

Question 2

The excel sheet provides the data for employees at ABC group.

a.Based on the current salaries,assign the employees a medical plan according to the criteria table in the excel sheet.

b.Count the number of employees in each state and fill the location summary table

c.Count the number of employees in each medical planand fillthemedicalplansummarytable

Question:Select the correct lookup function used for solving part (a)

1) =LOOKUP([@[Current Salary]],(0,25000,50000,100000,150000,200000,300000),("SINGLE2500","SINGLE1000","SPOUSE2500","SPOUSE1000","FAMILY2500","FAMILY1000"))

2) =LOOKUP([@[Current Salary]],{0,25000,50000,100000,150000,200000,300000},{SINGLE2500,SINGLE1000,SPOUSE2500,SPOUSE1000,FAMILY2500,FAMILY1000})

3) =LOOKUP([@[Current Salary]],{0,25000,50000,100000,150000,200000,300000},{"SINGLE2500","SINGLE1000","SPOUSE2500","SPOUSE1000","FAMILY2500","FAMILY1000"})

4) =LOOKUP([@[Current Salary]],{0,25000,25001,50000,50001,100000,100001,150000,150001,200000,200001,300000},{"SINGLE2500","SINGLE1000","SPOUSE2500","SPOUSE1000","FAMILY2500","FAMILY1000"})