Question

PLEASE HELP WITH THE PIVOT TABLE QUESTIONS AND SHOW EXCEL STEPS USED IN THE PROCESS. ***NOTE: PIVOT TABLE WILL BE POSTED IN SEPARATE 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 below.

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 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.

1. Use the Pivot Table Data 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.
2. 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?
   1. ___________Least amount sold:_____________
   2. ­­­­­­­­­­­­­­­­­­___________Largest amount sold:____________
3. What colors should Saeko buy?

Answer 1

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.

PivotTable Fields X Choose fields to add to report: Customer Color Name Color Type Yards Meters MORE TABLES... Drag fields between areas below Y FILTERS II COLUMNS Σ Values Σ VALUES ROWS Color Type Sum of Yards Sum of Meters ▼

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

Row LabelsSum of Yards Sum of Meters Black Blue Brown Green Purple Red Rose White Yellow Grand Total 2934 2929 2233 3515 945 954 1169 2592 762 18033 2682.8 2678.3 2041.8 3214.1 864.1 872.3 1068.9 2370.2 696.8 16489.3

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

Row LabelsSum of Yards Sum of Meters Black Blue Brown Green Purple Red Rose White Yellow Grand Total 2934 2929 2233 3515 945 954 1169 2592 762 18033 2682.8 2678.3 2041.8 3214.1 864.1 872.3 1068.9 2370.2 696.8 16489.3 min(B4:B12

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

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

Row Labels Black Blue Brown Green Purple Red Rose White Yellow Grand Total Sum of Yards Sum of Meters 2934 2682.8 Min 762 222 2951 3 Max 2678.3 Max -max(B4:B12 2233 3515 945 954 1169 2592 762 18033 2041.8 3214.1 864.1 872.3 1068.9 2370.2 696.8 16489.3

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.if you have any query just ask me how.i am here to helps you. dont give you direct thums down.if you satishfied to my work please give me vote for THUMS UP. THANK YOU SO MUCH