Question

1 Approved Answer

Posted on Sep 06, 2024

On the Transactions worksheet, in cell K10, enter a function that will return 0.05 if the customer used cash for the payment type and the

image text in transcribed On the Transactions worksheet, in cell K10, enter a function that will return 0.05 if the customer used cash for the payment type and the gross revenue was greater than $100, otherwise return a blank value (""). Use the fill handle to copy the formula down through cell K30.

On the Transactions worksheet, in cell P10, create a formula that will return 0.1 if the customer is a member and they spent more than $200 or if the customer simply purchased a promotional item, otherwise return a blank value (""). Format the cell as Percentage with 0 decimal places. Use the fill handle to copy the formula down through P30.

On the EmployeeAnalysis worksheet, in cell D14, use the IFS function to return Expert for training hours at least 40, return Proficient for training hours at least 25, otherwise return Beginner. Use the fill handle to copy the formula down through D18

image text in transcribed

Employees Golf Trainer Certification Daily Sales Goals EMP-ID EMP-00076 Ramal Hample EMP-00038 Pat Tyner Name Hours Status EMP-00076 EMP-00038 IF(logical test, [value_if_true], [valM-00024 EMP-00015 EMP-00026 Accessories Clothing Z00. S200.00 $ 300.00 65 CERTIFIED 100.00 JU. JUU.UU UU.UU 35 NOTcertified 200.00 $ 300.00 $300.00 700.00 $ 300.00 250.00 $ 200.00 $ 700.00$ 250.00 $ EMP-00024 Tyrone Thurma 58 CERTIFIED 500.00 $ 500.00 S clubs Shoes Total 67 CERTIFIED EMP-00015 Jerrold Wilcox 150.00 $ 150.00 $ 100.00 EMP-00026 Christy Yamme 25 NOTcertified 1,150.00 $ 1,250.00 1,450.00 1,450.00 $ 800.00 POS Training CommissionRates Revenue 3,500 $ 9% 2,500 $ Level EMP-ID Name Hours 4,500 EMP-00076 Ramal Hample EMP-00038 Pat Tyner Rate 5% 7% 11% 45 35 EMP-00024 Tyrone Thurma 30 EMP-00015 Jerrold Wilcox EMP-00026 Christy Yamme 20 25 Clubs Category Employee Daily Commission Employee Sales Goals EMP-ID EMP-00076 TotalRevenue Commission Sales Goal Job Title Col Num EMP-ID Name Row Num Ramal Hample Pat Tyner Tyrone Thurman Jerrold Wilcox Christy Yammel EMP-00076 Asst Manager $ 3,532.63 EMP-00038 Asst Manager $ 4,609.28 353.26312 EMP-00038 460.9278 EMP-00024 Associate EMP-00024 $2,618.79 1833.1495 EMP-00015 Associate $4,511.17 EMP-00015 4060.05354 EMP-00026 Associate EMP-00026 824.11 412.05575 Cut EWrap Text A A Comma [0] Calibri Comma Currency 11 Percentage E Copy Paste 00 Conditional Format as Currency [0 Formatting Table $ % Percent Insert De B IU IU- Merge & Center EEE Format Painter C Clipboard Font Alignment Number Styles f IF(C10="YES",0.05, H10>="100.00") K10 K D E F G H M O P Q 7 Reward Program Discounts 8 9 Quantity CouponCode clubMember? Promoltem? Gross Revenue MemberDisc CashDisc NetRevenue TotalDiscounts Rewards1 Rewards2 Coupon 3 T453X 15% 136.80 $ $ 65.90 $ No $ 10 S FALSE No 171.00 34.20 Yes $ 11 2 No 65.90 FALSE Yes $ $ 119.97 $ 12 129.00 7% 1 No FALSE 9.03 0.1 177.00 $ 309.60 $ $ No Yes $ S FALSE 13 3 177.00 3 T453X $ $ 387.00 15% 77.40 14 No No FALSE 3 T453X Yes $ 36.15 $ 46.35 7% 15% FALSE 15 No 10.20 0.1 Yes Yes $ $ 421.29 $ 453.00 7% 16 1 FALSE 31.71 0.1 12.00 $ 17 No No $ $ 12.00 FALSE 1 $ $ 108.30 $ 114.00 18 2 No No FALSE 5.70 $ 1,257.00 1,131.30 $ Yes $ $ 10% FALSE 19 3 C384R No 125.70 20 1 C384R 1T453X $ 1.80 $ 10% No No 2.00 FALSE 0.20 1.56 $ 142.36$ 16.96 $ Yes $ 7% 15% FALSE 0.44 21 No 2.00 0.1 3T382P $ Yes 5% 22 No 149.85 FALSE 7.49 3 T382P No $ No 5% FALSE 0.89 23 17.85 164.90 $ No $ 2 T453X No 15% FALSE 24 194.00 29.10 Yes $ 59.00 $ No 25 1 59.00 FALSE 26 27 Cut EWrap Text A A Comma [0] Calibri Comma Currency 11 Percentage E Copy Paste 00 Conditional Format as Currency [0 Formatting Table $ % Percent Insert De B IU IU- Merge & Center EEE Format Painter C Clipboard Font Alignment Number Styles f IF(C10="YES",0.05, H10>="100.00") K10 K D E F G H M O P Q 7 Reward Program Discounts 8 9 Quantity CouponCode clubMember? Promoltem? Gross Revenue MemberDisc CashDisc NetRevenue TotalDiscounts Rewards1 Rewards2 Coupon 3 T453X 15% 136.80 $ $ 65.90 $ No $ 10 S FALSE No 171.00 34.20 Yes $ 11 2 No 65.90 FALSE Yes $ $ 119.97 $ 12 129.00 7% 1 No FALSE 9.03 0.1 177.00 $ 309.60 $ $ No Yes $ S FALSE 13 3 177.00 3 T453X $ $ 387.00 15% 77.40 14 No No FALSE 3 T453X Yes $ 36.15 $ 46.35 7% 15% FALSE 15 No 10.20 0.1 Yes Yes $ $ 421.29 $ 453.00 7% 16 1 FALSE 31.71 0.1 12.00 $ 17 No No $ $ 12.00 FALSE 1 $ $ 108.30 $ 114.00 18 2 No No FALSE 5.70 $ 1,257.00 1,131.30 $ Yes $ $ 10% FALSE 19 3 C384R No 125.70 20 1 C384R 1T453X $ 1.80 $ 10% No No 2.00 FALSE 0.20 1.56 $ 142.36$ 16.96 $ Yes $ 7% 15% FALSE 0.44 21 No 2.00 0.1 3T382P $ Yes 5% 22 No 149.85 FALSE 7.49 3 T382P No $ No 5% FALSE 0.89 23 17.85 164.90 $ No $ 2 T453X No 15% FALSE 24 194.00 29.10 Yes $ 59.00 $ No 25 1 59.00 FALSE 26 27 Employees Golf Trainer Certification Daily Sales Goals EMP-ID EMP-00076 Ramal Hample EMP-00038 Pat Tyner Name Hours Status EMP-00076 EMP-00038 IF(logical test, [value_if_true], [valM-00024 EMP-00015 EMP-00026 Accessories Clothing Z00. S200.00 $ 300.00 65 CERTIFIED 100.00 JU. JUU.UU UU.UU 35 NOTcertified 200.00 $ 300.00 $300.00 700.00 $ 300.00 250.00 $ 200.00 $ 700.00$ 250.00 $ EMP-00024 Tyrone Thurma 58 CERTIFIED 500.00 $ 500.00 S clubs Shoes Total 67 CERTIFIED EMP-00015 Jerrold Wilcox 150.00 $ 150.00 $ 100.00 EMP-00026 Christy Yamme 25 NOTcertified 1,150.00 $ 1,250.00 1,450.00 1,450.00 $ 800.00 POS Training CommissionRates Revenue 3,500 $ 9% 2,500 $ Level EMP-ID Name Hours 4,500 EMP-00076 Ramal Hample EMP-00038 Pat Tyner Rate 5% 7% 11% 45 35 EMP-00024 Tyrone Thurma 30 EMP-00015 Jerrold Wilcox EMP-00026 Christy Yamme 20 25 Clubs Category Employee Daily Commission Employee Sales Goals EMP-ID EMP-00076 TotalRevenue Commission Sales Goal Job Title Col Num EMP-ID Name Row Num Ramal Hample Pat Tyner Tyrone Thurman Jerrold Wilcox Christy Yammel EMP-00076 Asst Manager $ 3,532.63 EMP-00038 Asst Manager $ 4,609.28 353.26312 EMP-00038 460.9278 EMP-00024 Associate EMP-00024 $2,618.79 1833.1495 EMP-00015 Associate $4,511.17 EMP-00015 4060.05354 EMP-00026 Associate EMP-00026 824.11 412.05575 Cut EWrap Text A A Comma [0] Calibri Comma Currency 11 Percentage E Copy Paste 00 Conditional Format as Currency [0 Formatting Table $ % Percent Insert De B IU IU- Merge & Center EEE Format Painter C Clipboard Font Alignment Number Styles f IF(C10="YES",0.05, H10>="100.00") K10 K D E F G H M O P Q 7 Reward Program Discounts 8 9 Quantity CouponCode clubMember? Promoltem? Gross Revenue MemberDisc CashDisc NetRevenue TotalDiscounts Rewards1 Rewards2 Coupon 3 T453X 15% 136.80 $ $ 65.90 $ No $ 10 S FALSE No 171.00 34.20 Yes $ 11 2 No 65.90 FALSE Yes $ $ 119.97 $ 12 129.00 7% 1 No FALSE 9.03 0.1 177.00 $ 309.60 $ $ No Yes $ S FALSE 13 3 177.00 3 T453X $ $ 387.00 15% 77.40 14 No No FALSE 3 T453X Yes $ 36.15 $ 46.35 7% 15% FALSE 15 No 10.20 0.1 Yes Yes $ $ 421.29 $ 453.00 7% 16 1 FALSE 31.71 0.1 12.00 $ 17 No No $ $ 12.00 FALSE 1 $ $ 108.30 $ 114.00 18 2 No No FALSE 5.70 $ 1,257.00 1,131.30 $ Yes $ $ 10% FALSE 19 3 C384R No 125.70 20 1 C384R 1T453X $ 1.80 $ 10% No No 2.00 FALSE 0.20 1.56 $ 142.36$ 16.96 $ Yes $ 7% 15% FALSE 0.44 21 No 2.00 0.1 3T382P $ Yes 5% 22 No 149.85 FALSE 7.49 3 T382P No $ No 5% FALSE 0.89 23 17.85 164.90 $ No $ 2 T453X No 15% FALSE 24 194.00 29.10 Yes $ 59.00 $ No 25 1 59.00 FALSE 26 27 Cut EWrap Text A A Comma [0] Calibri Comma Currency 11 Percentage E Copy Paste 00 Conditional Format as Currency [0 Formatting Table $ % Percent Insert De B IU IU- Merge & Center EEE Format Painter C Clipboard Font Alignment Number Styles f IF(C10="YES",0.05, H10>="100.00") K10 K D E F G H M O P Q 7 Reward Program Discounts 8 9 Quantity CouponCode clubMember? Promoltem? Gross Revenue MemberDisc CashDisc NetRevenue TotalDiscounts Rewards1 Rewards2 Coupon 3 T453X 15% 136.80 $ $ 65.90 $ No $ 10 S FALSE No 171.00 34.20 Yes $ 11 2 No 65.90 FALSE Yes $ $ 119.97 $ 12 129.00 7% 1 No FALSE 9.03 0.1 177.00 $ 309.60 $ $ No Yes $ S FALSE 13 3 177.00 3 T453X $ $ 387.00 15% 77.40 14 No No FALSE 3 T453X Yes $ 36.15 $ 46.35 7% 15% FALSE 15 No 10.20 0.1 Yes Yes $ $ 421.29 $ 453.00 7% 16 1 FALSE 31.71 0.1 12.00 $ 17 No No $ $ 12.00 FALSE 1 $ $ 108.30 $ 114.00 18 2 No No FALSE 5.70 $ 1,257.00 1,131.30 $ Yes $ $ 10% FALSE 19 3 C384R No 125.70 20 1 C384R 1T453X $ 1.80 $ 10% No No 2.00 FALSE 0.20 1.56 $ 142.36$ 16.96 $ Yes $ 7% 15% FALSE 0.44 21 No 2.00 0.1 3T382P $ Yes 5% 22 No 149.85 FALSE 7.49 3 T382P No $ No 5% FALSE 0.89 23 17.85 164.90 $ No $ 2 T453X No 15% FALSE 24 194.00 29.10 Yes $ 59.00 $ No 25 1 59.00 FALSE 26 27