Question

1 Approved Answer

Posted on Aug 20, 2024

2 1.5 Database functions allow for the user to specify criteria in one or more fields to explore the data with ease. When this is

2 1.5 Database functions allow for the user to specify criteria in one or more fields to explore the data with ease. When this is done, all the criteria must be evaluated to TRUE for the record to be included in the calculation. Using a table for the Excel Database allows you to add new records to the database easily and any database functions used on the table will automatically update. On the SalesData worksheet, convert the plain data set to an Excel table. Name the Excel table, SalesData and then create a named range, Sales Database, for all of the data in the table, including the column headings. Copy the column headings from the Sales Data table and paste them on the Database Totals worksheet, starting in cell A1 to setup the criteria area of for use in the Database functions. On the Database Totals worksheet, in cell B5, type NetRevenue for the field name that will be used in the database functions. In cells B7:B11, use the appropriate Database function to calculate the sum, average, count, max, and min of the NetRevenue field using the range A1:L2 as the criteria. Once all database functions have been created, use the criteria area to limit the calculations to Whose records with transaction dates after 11/15/2022 and with Apple Pay as the payment method. Finally, change the field being used in the calculations from NetRevenue to TotalDiscounts, All Stocks Geography A1 Xv fx Z L NS Sort Filter Pro Shop Sales Database H K - M 4 Columng Transit 15% 9 $ 15% 15% 1 2 Column7 Column8 3 TransID Transit $ 171.00 No 5 $ 65.90 No 6 $ 129.00 Yes 7 $ 177.00 No 8 $ 387.00 No 46.35 Yes 10 $ 453.00 Yes 11 $ 12.00 No 12 $ 114.00 No 13 $ 1,257.00 No 14 $ 2.00 No 15 $ 2.00 Yes 16 $ 149.85 No 17 S 17.85 No 18 $ 194.00 No 19 $ 59.00 No 20 $ 46.35 No 21 $ 838.00 Yes 22 $ 23.80 No 23 $ 129.00 Yes 24 $ 838.00 No 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Sales Data 10% 10% 15% 5% 5% 15% Column10 Column11 Column12 Cash Disc Net Revenue TotalDiscounts 5% $ 136.80 $ 34.20 $ 65.90 $ $ 119.97 $ 9.03 $ 177.00 $ 5% $ 309.60 $ 77.40 $ 36.15 $ 10.20 $ 421.29 $ 31.71 $ 12.00 $ 5% $ 108.30 $ 5.70 $ 1,131.30 $ 125.70 $ 1.80 S 0.20 $ 1.56 $ 0.44 $ 142.36 $ 7.49 $ 16.96 $ 0.89 $ 164.90 $ 29.10 $ 59.00 $ $ 46.35 S 779.34 $ 58.66 $ 23.80 $ $ 113.52 $ 15.48 $ 838.00 $ 5% Database Totals Ready Tell me IL 21 21 Get External Data Refresh All Stocks Geography Sort Filter B7 fx =SUM(SalesDatalK4:K24) A B D E 1 Pro Shop Sales Database 2 3 NetRevenue $ $ 4,705.90 224. 21 1,131.30 1.56 $ $ 5 Field 6 7 Sum 8 Average 9 Count 10 Max 11 Min 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 Sales Data Database Tots Ready A 1600 3 2 1.5 Database functions allow for the user to specify criteria in one or more fields to explore the data with ease. When this is done, all the criteria must be evaluated to TRUE for the record to be included in the calculation. Using a table for the Excel Database allows you to add new records to the database easily and any database functions used on the table will automatically update. On the SalesData worksheet, convert the plain data set to an Excel table. Name the Excel table, SalesData and then create a named range, Sales Database, for all of the data in the table, including the column headings. Copy the column headings from the Sales Data table and paste them on the Database Totals worksheet, starting in cell A1 to setup the criteria area of for use in the Database functions. On the Database Totals worksheet, in cell B5, type NetRevenue for the field name that will be used in the database functions. In cells B7:B11, use the appropriate Database function to calculate the sum, average, count, max, and min of the NetRevenue field using the range A1:L2 as the criteria. Once all database functions have been created, use the criteria area to limit the calculations to Whose records with transaction dates after 11/15/2022 and with Apple Pay as the payment method. Finally, change the field being used in the calculations from NetRevenue to TotalDiscounts, All Stocks Geography A1 Xv fx Z L NS Sort Filter Pro Shop Sales Database H K - M 4 Columng Transit 15% 9 $ 15% 15% 1 2 Column7 Column8 3 TransID Transit $ 171.00 No 5 $ 65.90 No 6 $ 129.00 Yes 7 $ 177.00 No 8 $ 387.00 No 46.35 Yes 10 $ 453.00 Yes 11 $ 12.00 No 12 $ 114.00 No 13 $ 1,257.00 No 14 $ 2.00 No 15 $ 2.00 Yes 16 $ 149.85 No 17 S 17.85 No 18 $ 194.00 No 19 $ 59.00 No 20 $ 46.35 No 21 $ 838.00 Yes 22 $ 23.80 No 23 $ 129.00 Yes 24 $ 838.00 No 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Sales Data 10% 10% 15% 5% 5% 15% Column10 Column11 Column12 Cash Disc Net Revenue TotalDiscounts 5% $ 136.80 $ 34.20 $ 65.90 $ $ 119.97 $ 9.03 $ 177.00 $ 5% $ 309.60 $ 77.40 $ 36.15 $ 10.20 $ 421.29 $ 31.71 $ 12.00 $ 5% $ 108.30 $ 5.70 $ 1,131.30 $ 125.70 $ 1.80 S 0.20 $ 1.56 $ 0.44 $ 142.36 $ 7.49 $ 16.96 $ 0.89 $ 164.90 $ 29.10 $ 59.00 $ $ 46.35 S 779.34 $ 58.66 $ 23.80 $ $ 113.52 $ 15.48 $ 838.00 $ 5% Database Totals Ready Tell me IL 21 21 Get External Data Refresh All Stocks Geography Sort Filter B7 fx =SUM(SalesDatalK4:K24) A B D E 1 Pro Shop Sales Database 2 3 NetRevenue $ $ 4,705.90 224. 21 1,131.30 1.56 $ $ 5 Field 6 7 Sum 8 Average 9 Count 10 Max 11 Min 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 Sales Data Database Tots Ready A 1600 3