See how to use Excel Formulas to analyze a group of customers who have been given coupons to three stores. Our goal is to count how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons) and then extract the customer names in each group. The data and goals are:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
5) Goal 2: List Customers who visited a given set of stores.
Download File: http://ift.tt/2tvSeEE
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics in Video:
1. (00:12) Introduction to Problem
2. (03:33) Count number of coupons each customer redeemed for each store. See the COUNTIFS Function.
3. (07:03) Logical formulas for determining which customers fall into each of the eight coupon groups. Learn about AND Logical Tests, OR Logical Tests, and a NONE Logical Test (None are TRUE, or All are FALSE). See the Excel Spreadsheet Logical functions: AND, OR, NOT.
4. (17:23) Count Customers in each of the eight coupon groups using COUNTIFS Function.
5. (18:02) Formula to Extract Customer Names for each of the eight coupon groups. Learn about Array Formulas to looks an item and Return Multiple Items. Learn about the Excel Spreadsheet functions: ROW, AGGREGATE, ROWS, INDEX and IF.
6. (29:25) Conclusion