Problem 7: The bank data shown in the table is from a promotion conducted by a bank during the month of September. Each record represents a transaction of: an Existing or New customer; an opening of an account type (Savings, CD, etc.); in a particular $Amount; at a particular branch (Central, Westside, etc.); the account transaction was performed by a Teller or a New Accts agent. Data is also collected on the customers age and the day of the week the account was opened. a) What is the 67st percentile of the $Amount using the percentile function (use the Excel ".INC" version)? b) What is the rank of the 67st percentile of the $Amount using the common formula (in our text) for calculating the rank of the Kth percentile? c) What is the 67st percentile of the $Amount using the Rank and Percentile Data Analysis tool in Excel? d) What is the interquartile range of the $Amount data. LL D E F G H Age of $Amount Acct Type Opened By Branch Customer 1 Cust 2 84,064 Savings New Accts Central Existing 31 3 75,664 Checking New Accts Central Existing 69 4 87,638 Savings New Accts Central Existing 42 5 2,902 Checking Teller Central Existing 68 6 10,409 CD New Accts Westside New 84 7 1,490 CD Teller Westside Existing 65 8 88,631 Savings 83 New Accts North County Existing New Accts 9 40,593 CD Central Existing 83 10 83,442 CD 69 New Accts North County Existing New Accts 11 1,189 CD Central New 78 12 3,410 CD 24 13 43,424 Checking New Accts North County Existing New Accts Central Existing New Accts Central 83 14 59,925 Checking Existing 47 15 73,268 CD New Accts Westside Existing 79 16 16,451 Savings North County New 85 New Accts Teller 17 53,270 Checking Central Existing 68 18 25,186 Savings New Accts Westside Existing 59 19 8,411 Checking New Accts Central Existing 28 20 11,378 Checking New Accts Westside Existing 51 21 74,422 CD New Accts North County Existing 72 22 11,952 IRA New Accts Westside Existing 84 23 43,739 Checking New Accts Westside Existing 59 24 73,680 Checking New Accts Central Existing 51 25 38,954 CD New Accts Westside New 69 26 56,056 Checking New Accts North County Existing 68 27 85,115 CD New Accts North County New 64 DOW Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Thu Thu Thu Thu Thu Thu 28 29 30 31 32 33 34 35 36 37 38 39 O 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 A 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 B 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 19,360 42,904 25,237 32,787 76,834 10,820 14,783 60,196 89,564 51,173 25,385 46,169 21,448 57,051 87,246 965 60,693 32,636 64,510 29,419 20,532 13,478 68,463 25,516 40,181 38,407 69,507 76,074 E F G H D CD New Accts North County New 74 Savings New Accts Central Existing 51 CD New Accts Central Existing 44 CD New Accts Central New 32 CD Westside Existing 84 New Accts Teller IRA Central Existing 61 CD New Accts Central New 67 Checking New Accts Central Existing 83 Checking New Accts North County Existing 44 CD New Accts Central Existing 91 Savings New Accts Westside Existing 64 Savings Teller North County Existing 25 CD New Accts Central Existing 62 Checking New Accts Central Existing 75 Checking New Accts Central Existing 48 Savings New Accts North County Existing 28 Checking New Accts 27 Central New Westside Existing Checking New Accts 72 Checking Teller North County Existing 43 New Accts North County Existing 45 Checking IRA New Accts North County Existing 87 Checking Teller North County Existing 49 Checking New Accts North County Existing 76 Central Existing Checking New Accts 31 Checking New Accts Central Existing 51 Checking New Accts Central Existing 43 New Accts North County Existing 37 IRA CD New Accts North County Existing 50 I Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Fri Fri EEEEEEEEEE Problem 7: The bank data shown in the table is from a promotion conducted by a bank during the month of September. Each record represents a transaction of: an Existing or New customer; an opening of an account type (Savings, CD, etc.); in a particular $Amount; at a particular branch (Central, Westside, etc.); the account transaction was performed by a Teller or a New Accts agent. Data is also collected on the customers age and the day of the week the account was opened. a) What is the 67st percentile of the $Amount using the percentile function (use the Excel ".INC" version)? b) What is the rank of the 67st percentile of the $Amount using the common formula (in our text) for calculating the rank of the Kth percentile? c) What is the 67st percentile of the $Amount using the Rank and Percentile Data Analysis tool in Excel? d) What is the interquartile range of the $Amount data. LL D E F G H Age of $Amount Acct Type Opened By Branch Customer 1 Cust 2 84,064 Savings New Accts Central Existing 31 3 75,664 Checking New Accts Central Existing 69 4 87,638 Savings New Accts Central Existing 42 5 2,902 Checking Teller Central Existing 68 6 10,409 CD New Accts Westside New 84 7 1,490 CD Teller Westside Existing 65 8 88,631 Savings 83 New Accts North County Existing New Accts 9 40,593 CD Central Existing 83 10 83,442 CD 69 New Accts North County Existing New Accts 11 1,189 CD Central New 78 12 3,410 CD 24 13 43,424 Checking New Accts North County Existing New Accts Central Existing New Accts Central 83 14 59,925 Checking Existing 47 15 73,268 CD New Accts Westside Existing 79 16 16,451 Savings North County New 85 New Accts Teller 17 53,270 Checking Central Existing 68 18 25,186 Savings New Accts Westside Existing 59 19 8,411 Checking New Accts Central Existing 28 20 11,378 Checking New Accts Westside Existing 51 21 74,422 CD New Accts North County Existing 72 22 11,952 IRA New Accts Westside Existing 84 23 43,739 Checking New Accts Westside Existing 59 24 73,680 Checking New Accts Central Existing 51 25 38,954 CD New Accts Westside New 69 26 56,056 Checking New Accts North County Existing 68 27 85,115 CD New Accts North County New 64 DOW Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Mon Thu Thu Thu Thu Thu Thu 28 29 30 31 32 33 34 35 36 37 38 39 O 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 A 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 B 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/6 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 9/7 19,360 42,904 25,237 32,787 76,834 10,820 14,783 60,196 89,564 51,173 25,385 46,169 21,448 57,051 87,246 965 60,693 32,636 64,510 29,419 20,532 13,478 68,463 25,516 40,181 38,407 69,507 76,074 E F G H D CD New Accts North County New 74 Savings New Accts Central Existing 51 CD New Accts Central Existing 44 CD New Accts Central New 32 CD Westside Existing 84 New Accts Teller IRA Central Existing 61 CD New Accts Central New 67 Checking New Accts Central Existing 83 Checking New Accts North County Existing 44 CD New Accts Central Existing 91 Savings New Accts Westside Existing 64 Savings Teller North County Existing 25 CD New Accts Central Existing 62 Checking New Accts Central Existing 75 Checking New Accts Central Existing 48 Savings New Accts North County Existing 28 Checking New Accts 27 Central New Westside Existing Checking New Accts 72 Checking Teller North County Existing 43 New Accts North County Existing 45 Checking IRA New Accts North County Existing 87 Checking Teller North County Existing 49 Checking New Accts North County Existing 76 Central Existing Checking New Accts 31 Checking New Accts Central Existing 51 Checking New Accts Central Existing 43 New Accts North County Existing 37 IRA CD New Accts North County Existing 50 I Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Thu Fri Fri EEEEEEEEEE