A current buzzword in the technology industry is Analytics. This is the analysis
ID: 3822258 • Letter: A
Question
A current buzzword in the technology industry is Analytics. This is the analysis (discovery), explanation (interpretation), and communication (dissemination) of information – especially relating to patterns and trends in historical data. Analytics encompasses Computer Science, Mathematics (statistics),and Business, (operations research) to help with decision making.
Perform the following analytics on the data in the linked spreadsheed Assignment 8.xlsx:
On the first copy of the spreadsheet, determine the total sales from each of the three regions (East, Central, West). Choose an appropriate chart type and include that chart on the spreadsheet.
On the second copy of the spreadsheet (you will need to make this copy), determine the total sales from each of the salespeople. Choose an appropriate chart type and include that chart on the spreadsheet.
In order to determine the total sales in each spreadsheet, use a spreadsheet function - don't manually choose the cells to add to the total. If this was a larger spreadsheet, with tens of thousands of rows, it would be too time-consuming to perform this task manually.
Hint: consider the process you would follow to solve this problem "by hand." If you wanted to add up total sales from the Central region, you might ask this question as you consider each line: "Is this line related to the Central region?" If the answer to the question was "yes," you would add the sales to a running total. In other words, you only want to add to the sum if a condition is true. There is a function in Excel (very closely related to the sum function) that will allow you to ask this question...
Remember the concepts of useful information and ensure your spreadsheets are both accurate and formatted (understandable to a user).
Central
Jardine
Desk
4
125.00
500.00
East
Jones
Pencil
95
1.99
189.05
Central
Kivell
Binder
50
19.99
999.50
West
Thompson
Binder
32
19.99
639.68
Central
Jardine
Pencil
36
4.99
179.64
Central
Gill
Pen
27
19.99
539.73
West
Sorvino
Pencil
56
2.99
167.44
East
Jones
Binder
60
4.99
299.40
Central
Andrews
Pencil
75
1.99
149.25
Central
Jardine
Pencil
90
4.99
449.10
West
Thompson
Pencil
32
1.99
63.68
East
Jones
Binder
60
8.99
539.40
Central
Morgan
Pencil
90
4.99
449.10
East
Howard
Binder
29
1.99
57.71
East
Parent
Binder
81
19.99
1,619.19
East
Jones
Pencil
35
4.99
174.65
Central
Smith
Desk
2
125.00
250.00
East
Jones
Pen Set
16
15.99
255.84
Central
Morgan
Binder
28
8.99
251.72
East
Jones
Pen
64
8.99
575.36
East
Parent
Pen
15
19.99
299.85
Central
Kivell
Pen Set
96
4.99
479.04
Central
Smith
Pencil
67
1.29
86.43
East
Parent
Pen Set
74
15.99
1,183.26
Central
Gill
Binder
46
8.99
413.54
Central
Smith
Binder
87
15.00
1,305.00
East
Jones
Binder
4
4.99
19.96
West
Sorvino
Binder
7
19.99
139.93
Central
Jardine
Pen Set
50
4.99
249.50
Central
Andrews
Pencil
66
1.99
131.34
East
Howard
Pen
96
4.99
479.04
Central
Gill
Pencil
53
1.29
68.37
Central
Gill
Binder
80
8.99
719.20
Central
Kivell
Desk
5
125.00
625.00
East
Jones
Pen Set
62
4.99
309.38
Central
Morgan
Pen Set
55
12.49
686.95
Central
Kivell
Pen Set
42
23.95
1,005.90
West
Sorvino
Desk
3
275.00
825.00
Central
Gill
Pencil
7
1.29
9.03
West
Sorvino
Pen
76
1.99
151.24
West
Thompson
Binder
57
19.99
1,139.43
Central
Andrews
Pencil
14
1.29
18.06
Central
Jardine
Binder
11
4.99
54.89
Central
Jardine
Binder
76
19.99
1,519.24
Central
Andrews
Binder
28
4.99
139.72
Explanation / Answer
please find it here:
https://drive.google.com/file/d/0B4BvF-maTxLUX0c4dU9Pek5wVXM/view
Please download it and view in Ms-Excel.
For sum, the function used is: SUMIFS
For eg, SUMIFS(F1:F45,A1:A45,"Central") means that
sum of F1 to F45 where (for A1 to A45, the cell value is "Central")