Question

1 Approved Answer

Posted on Aug 15, 2024

This is similar to Worksheet 2, but now you have randomizing criteria for your conditions, which are in cells O11:O14. In the green cells calculate

image text in transcribed

image text in transcribed This is similar to Worksheet 2, but now you have randomizing criteria for your conditions, which are in cells O11:O14. In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the condition(s) as specified, where X is the condition that changes every time you execute something in Excel. Your formula should work regardless of the conditions changing.

A B G H K M 4 5 6 7 8 9 9 10 11 12 13 14 Salesperson SP-20 SP-17 SP-05 SP-08 SP-08 SP-21 SP-02 SP-19 SP-12 SP-17 SP-05 SP-12 SP-07 SP-04 SP-09 SP-01 SP-15 SP-16 # Units 100 82 88 89 3 48 79 85 64 80 Date 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 15 16 17 18 19 20 21 22 23 24 Customer CUST-035 CUST-021 - CUST-130 CUST-019 CUST-076 CUST-085 CUST-146 CUST-048 CUST-053 CUST-072 CUST-051 CUST-123 CUST-012 CUST-145 CUST-037 CUST-060 CUST-128 CUST-145 CUST-078 CUST-143 CUST-133 CUST-026 CUST-146 CUST-001 CUST-121 CUST-083 D Product PROD-005 PROD-008 PROD-009 PROD-012 PROD-016 PROD-023 PROD-053 PROD-053 PROD-059 PROD-065 PROD-072 PROD-073 PROD-076 PROD-086 PROD-087 PROD-090 PROD-093 PROD-097 PROD-103 PROD-111 PROD-116 PROD-138 PROD-144 PROD-145 PROD-146 PROD-148 Warehouse MSA-13 MSA-42 MSA-61 MSA-06 MSA-16 MSA-74 MSA-31 MSA-35 MSA-39 MSA-01 MSA-42 MSA-32 MSA-02 MSA-57 MSA-11 MSA-10 MSA-49 E Material MATL-Citrine MATL-Aquamarine MATL-Emerald MATL-Moonstone MATL-Copper MATL-Jasper MATL-Silver MATL-Silver MATL-Amethyst MATL-Moonstone MATL-Garnet MATL-Tanzanite MATL-Ruby MATL-Silver MATL-Quartz MATL-Silver MATL-Opal MATL-Opal MATL-Citrine MATL-Quartz MATL-Opal MATL-Jasper MATL-Sapphire MATL-Emerald MATL-Bronze MATL-Bronze F Size SIZE-S SIZE-S SIZE-S SIZE-L SIZE-L SIZE-S SIZE-XS SIZE-M SIZE-S SIZE-XL SIZE-XS SIZE-S SIZE-XL SIZE-S SIZE-S SIZE-S SIZE-XS SIZE-XL SIZE-L SIZE-M SIZE-L SIZE-L SIZE-XS SIZE-XS SIZE-XL SIZE-S 52 30 46 22 86 76 53 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Sales 524,020 $ 415,680 $ 456,541 $ 21,720 35,134 $ 53,816 $ 870,232 $ 352,612 $ 5,568 $ 399,050 $ 387,309 $ 911,271 $ 3,204 | s 711,134 $ 166,804 $ 431,104 $ 287,850 $ 141,082 $ 91,372 $ 762,734 $ 242,465 $ 305,712 $ 225,254 $ 151,280$ 501,840 $ 238,556 $ $ COGS Collections 373,708 $ 524,020 286,510$ 415,680 254,174 $ 228,269 9,222 $ 21,720 24,258 $ 35,134 35,859 $ 53,816 519,937 $ 870,232 241,911 $ 352,612 1,806 $ 5,568 248,241 $ 399,050 298,717 $ 387,309 704,058$ 911,271 1,643 $ 3,204 452,503 $ 711,134 121,723 $ 166,804 278,177 $ 431,104 120,680 $ 287,850 63,237 $ 141,082 44,960$ 91,372 607,032 $ 762.734 99,286 $ 242,465 169,770 $ 305,712 143,700 $ 225,254 70,374 $ 151,280 277,006 $ 501,840 139,282 $ 238,556 71 46 22 25 12 44 26 27 28 29 30 MSA-62 MSA-44 MSA-32 MSA-18 MSA-20 MSA-36 MSA-35 MSA-03 MSA-70 SP-13 SP-11 SP-05 SP-16 SP-04 SP-19 SP-01 SP-02 8 92 40 15 AIRS An Gross Profit All products: Size X only: Material X and Product X only: Customer X for Quarter 3 only: Conditions Criteria Customer: Customer 4 S Size: Material: Peridot Product: Product 10 A B G H K M 4 5 6 7 8 9 9 10 11 12 13 14 Salesperson SP-20 SP-17 SP-05 SP-08 SP-08 SP-21 SP-02 SP-19 SP-12 SP-17 SP-05 SP-12 SP-07 SP-04 SP-09 SP-01 SP-15 SP-16 # Units 100 82 88 89 3 48 79 85 64 80 Date 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 1/1/19 15 16 17 18 19 20 21 22 23 24 Customer CUST-035 CUST-021 - CUST-130 CUST-019 CUST-076 CUST-085 CUST-146 CUST-048 CUST-053 CUST-072 CUST-051 CUST-123 CUST-012 CUST-145 CUST-037 CUST-060 CUST-128 CUST-145 CUST-078 CUST-143 CUST-133 CUST-026 CUST-146 CUST-001 CUST-121 CUST-083 D Product PROD-005 PROD-008 PROD-009 PROD-012 PROD-016 PROD-023 PROD-053 PROD-053 PROD-059 PROD-065 PROD-072 PROD-073 PROD-076 PROD-086 PROD-087 PROD-090 PROD-093 PROD-097 PROD-103 PROD-111 PROD-116 PROD-138 PROD-144 PROD-145 PROD-146 PROD-148 Warehouse MSA-13 MSA-42 MSA-61 MSA-06 MSA-16 MSA-74 MSA-31 MSA-35 MSA-39 MSA-01 MSA-42 MSA-32 MSA-02 MSA-57 MSA-11 MSA-10 MSA-49 E Material MATL-Citrine MATL-Aquamarine MATL-Emerald MATL-Moonstone MATL-Copper MATL-Jasper MATL-Silver MATL-Silver MATL-Amethyst MATL-Moonstone MATL-Garnet MATL-Tanzanite MATL-Ruby MATL-Silver MATL-Quartz MATL-Silver MATL-Opal MATL-Opal MATL-Citrine MATL-Quartz MATL-Opal MATL-Jasper MATL-Sapphire MATL-Emerald MATL-Bronze MATL-Bronze F Size SIZE-S SIZE-S SIZE-S SIZE-L SIZE-L SIZE-S SIZE-XS SIZE-M SIZE-S SIZE-XL SIZE-XS SIZE-S SIZE-XL SIZE-S SIZE-S SIZE-S SIZE-XS SIZE-XL SIZE-L SIZE-M SIZE-L SIZE-L SIZE-XS SIZE-XS SIZE-XL SIZE-S 52 30 46 22 86 76 53 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Sales 524,020 $ 415,680 $ 456,541 $ 21,720 35,134 $ 53,816 $ 870,232 $ 352,612 $ 5,568 $ 399,050 $ 387,309 $ 911,271 $ 3,204 | s 711,134 $ 166,804 $ 431,104 $ 287,850 $ 141,082 $ 91,372 $ 762,734 $ 242,465 $ 305,712 $ 225,254 $ 151,280$ 501,840 $ 238,556 $ $ COGS Collections 373,708 $ 524,020 286,510$ 415,680 254,174 $ 228,269 9,222 $ 21,720 24,258 $ 35,134 35,859 $ 53,816 519,937 $ 870,232 241,911 $ 352,612 1,806 $ 5,568 248,241 $ 399,050 298,717 $ 387,309 704,058$ 911,271 1,643 $ 3,204 452,503 $ 711,134 121,723 $ 166,804 278,177 $ 431,104 120,680 $ 287,850 63,237 $ 141,082 44,960$ 91,372 607,032 $ 762.734 99,286 $ 242,465 169,770 $ 305,712 143,700 $ 225,254 70,374 $ 151,280 277,006 $ 501,840 139,282 $ 238,556 71 46 22 25 12 44 26 27 28 29 30 MSA-62 MSA-44 MSA-32 MSA-18 MSA-20 MSA-36 MSA-35 MSA-03 MSA-70 SP-13 SP-11 SP-05 SP-16 SP-04 SP-19 SP-01 SP-02 8 92 40 15 AIRS An Gross Profit All products: Size X only: Material X and Product X only: Customer X for Quarter 3 only: Conditions Criteria Customer: Customer 4 S Size: Material: Peridot Product: Product 10