Question

Saeko has a yarn shop and wants to expand the colors in her yarn lines. She sends you the raw data in the "Pivot Table Data" tab. This data consists of the number of yards and meters of the color names, and the color types that each name belongs to, sold during her busiest weekend last year. Use the "Pivot Table Data" tab to create a pivot table that shows Saeko the number of yards and meters that were sold in the various yarn types during the busiest weekend of her shop last year. 7) Using the pivot table that you just created, determine which of the color types sold the least amount of yards using =MIN() Using the pivot table that you just created, determine which of the color types sold the largest amount of yards using =MAX() If Saeko wants to expand the colors in both the best selling and the worst selling color type, which two color types should she buy? 8) Least amount sold: 9) Largest amount sold: 10) What colors should Saeko buy? Customer Color Name Color Type Yards Meters 1 Coriander White 651 595.3 2 Black Black 211 192.9 3 Daffodil Yellow 762 696.8 4 Black Black 735 672.1 5 Opal Blue 551 503.8 6 Toffee Brown 709 648.3 7 Ruby Red 792 724.2 8 Ash Blue 830 759.0 9 Black Black 530 484.6 10 Ash Blue 292 267.0 11 Black Black 744 680.3 12 Whirlpool Blue 943 862.3 13 Verde Green 947 865.9 14 Regal Purple 945 864.1 15 Lynx Brown 774 707.7 16 Yellow Rose Yellow 801 732.4 17 Chocolate Brown 750 685.8 18 Mist White 629 575.2 19 Whirlpool Blue 113 103.3 20 Alfalfa Green 344 314.6 21 Ruby Red 162 148.1 22 Verde Green 964 881.5 23 Sky White 313 286.2 24 Black Black 223 203.9 25 Whirlpool Blue 200 182.9 26 Black Black 491 449.0 27 Mist White 999 913.5 28 Alfalfa Green 598 546.8 29 Jade Green 662 605.3 30 Yellow Rose Yellow 368 336.5 31 Cream White 529 483.7 32 Black Black 513 469.1 33 Ruby Red 870 795.5 34 Mist White 342 312.7 35 Yellow Rose Yellow 747 683.1 36 Black Black 160 146.3 37 Sky White 628 574.2 38 Periwinkle Purple 185 169.2 39 Coriander White 978 894.3 40 Black Black 607 555.0 41 Yellow Rose Yellow 387 353.9 42 Black Black 255 233.2 43 Periwinkle Purple 742 678.5 44 Black Black 414 378.6 45 Blush Red 345 315.5 46 Black Black 892 815.6 47 Mist White 727 664.8 48 Coriander White 584 534.0 49 Cream White 321 293.5 50 Verde Green 478 437.1 51 Black Black 131 119.8 52 Daffodil Yellow 539 492.9 53 Chocolate Brown 767 701.3 54 Daffodil Yellow 369 337.4 55 Regal Purple 378 345.6 56 Daffodil Yellow 376 343.8 57 Coriander White 957 875.1 58 Black Black 929 849.5 59 Black Black 959 876.9 60 Lynx Brown 994 908.9 61 Periwinkle Purple 714 652.9 62 Daffodil Yellow 912 833.9 63 Coriander White 776 709.6 64 Verde Green 895 818.4 65 Lynx Brown 706 645.6 66 Alfalfa Green 105 96.0 67 Cream White 165 150.9 68 Daffodil Yellow 505 461.8 69 Periwinkle Purple 661 604.4 70 Cream White 226 206.7 71 Black Black 472 431.6 72 Opal Blue 184 168.2 73 Cream White 191 174.7 74 Cream White 238 217.6 75 Coriander White 894 817.5 76 Mist White 488 446.2 77 Verde Green 400 365.8 78 Black Black 618 565.1 79 Blush Red 374 342.0 80 Opal Blue 359 328.3 81 Daffodil Yellow 553 505.7 82 Mist White 439 401.4 83 Alfalfa Green 448 409.7 84 Jade Green 290 265.2 85 Ruby Red 907 829.4 86 Verde Green 402 367.6 87 Cream White 123 112.5 88 Black Black 981 897.0 89 Chocolate Brown 247 225.9 90 Regal Purple 805 736.1 91 Sky White 358 327.4 92 Regal Purple 461 421.5 93 Yellow Rose Yellow 540 493.8 94 Lynx Brown 964 881.5 95 Regal Purple 317 289.9 96 Ash Blue 190 173.7 97 Periwinkle Purple 423 386.8 98 Black Black 761 695.9 99 Black Black 219 200.3 100 Blush Red 258 235.9 101 Regal Purple 968 885.1 102 Sapphire Blue 517 472.7 103 Whirlpool Blue 362 331.0 104 Yellow Rose Yellow 125 114.3 105 Whirlpool Blue 504 460.9 106 Sapphire Blue 427 390.4 107 Chocolate Brown 557 509.3 108 Toffee Brown 498 455.4 109 Sapphire Blue 216 197.5 110 Whirlpool Blue 174 159.1 111 Black Black 470 429.8 112 Regal Purple 644 588.9 113 Opal Blue 436 398.7 114 Blush Red 768 702.3 115 Coriander White 456 417.0 116 Whirlpool Blue 385 352.0 117 Ash Blue 869 794.6 118 Blush Red 416 380.4 119 Black Black 634 579.7 120 Ruby Red 302 276.1 121 Mist White 892 815.6 122 Chocolate Brown 240 219.5 123 Opal Blue 523 478.2 124 Toffee Brown 710 649.2 125 Yellow Rose Yellow 245 224.0

Answer 1

Hi,

As the data was not in excel format, it was difficult to paste it in excel. So I have taken the first 30 rows as sample data. Based on the sample data, I will try to solve the problem and you can replicate the same steps to solve the problems on the entire data set.

Steps to create Pivot from the excel data.

1. Select the columns and rows on which you want to create the pivot table.

2. Click on Insert - Pivot Table

3. It will by default show the selected table range and the pivot table to be created on new work sheet.

4. Click on Ok.

5. In the pivot table fields, drag color type into rows and yards, meters into values as shown below. Select sum of yards and meters.

7. The following Pivot table shows the number of yards and meters sold for various colour types from the sample data.

B. the min() function should be used as shown below to find the color type which sold least

It returns the value of 762 which corresponds to Yellow color type.

Maximum color sold can be found by using max() function below.

Max yards sold is 3515 which belongs to Green color.

So the least selling color is yellow and maximum selling color is Green.

If Seiko wants to invest in least and max selling colors, then he should invest in Yellow and Green.

Please note the above solution was based on sample data using first 30 rows. You should follow the same steps to arrive at solution for the entire data set.

Hope it helps.