Question

can someone pls help me with this probke?

Problem 2: Worksheet 'customers' has the R (recency), F (frequency), and M (Monetary value) related information for the representative 10 customers of a home store. Recency is the number of months since the last purchase made by the customer. Frequency is the average number of times the customer comes to the store for purchase. Monetary value is the average dollar value of the transactions that the customer makes with the store. The home store has decided to introduce loyalty programs to the customers whose recency is not more than 6 months, frequency is at least 4 times a year, and the average monetary value is more than $150. The store wants to offer cashback promotion to such loyal customers. For the rest of the customers, the store wants to offer coupons based promotion. Use appropriate formula to determine the type of promotion offered to each customer in the Promotion Type column of the worksheet 20 Points

Answer 1

We can use the conditional formatting function (IF function) available in MS Excel for these type of problems .

This function takes different conditions provided in the formula and checks them with the concerned cells and if the answer is true a particular answer wil be shown in our worksheet (in this case "cashback") and if the answer is false another answer provided by us will be shown(in our case"coupon").

The formula mentioned below could be inserted in the formula bar and it will give the desired results in the column Promotion Type. just make sure to select the cell where you want the answer.

Hope it helped.

=IF(B2<=6,IF(C2>=4,IF(D2>150,"cashback","coupon"),"coupon"),"coupon")