ComputerScienceExpert
$18/per page/
You'll be using Excel 2016 and no hard coding on this assignment.
Please and thank you!
CSC 141 - Assignment 1.2- Cross Worksheet Skills Copy and paste one worksheet to another
Group worksheets
Make a reference to a cell on another worksheet
Select the correct combination of relative, absolute and mixed references so that a formula can
be copied and still work properly.
Basic formatting.
Research and use Excel MID and CELL functions. Preparation
Download the file YourName_Assignment_1.2.xlsx and be sure to put your name in the filename
following the instructions in the General Homework Instructions. Don’t delete or add any rows or columns to the existing worksheet.
Be sure you use the exact cells referenced in the instructions. Note As you work, where you need to write a formula, create your own cell
names as appropriate so that every formula you write uses only your
names. For example, =SUM(SomeNameYouMakeUp) instead of
=SUM(Z3:Z17). If you’re not sure how to do this see the video on
naming ranges! Intro
You’re new in your job as an assistant analyst at Acme widgets. You’re working for the chief financial
officer. She just emailed you a spreadsheet with two months’ worth of numbers and a scanned in
document. The scanned in document is named MeetingNotes.pdf on Blackboard. Steps
1. [3] To the workbook add worksheets for November 2016 and December 2016. Place them
to the right of the October sheet so the months are in order. Name these worksheets
similarly to the existing worksheet and give them the same layout. 2. [3] Then enter the data from her scanned-in document into the worksheets for the two
new months.
Be sure to double check your data entry. At Acme data entry errors are inexcusable;
your predecessor was fired because an important spreadsheet had incorrect numbers
entered in it and management made a bad decision based on the spreadsheet. 1|Page Professor Shoemaker CSC 141 - Assignment 1.2- Cross Worksheet
3. [2] In column F of all the worksheets create a Total column that sums the Sales Amount
and Freight dollars for each row. 4. [5] Create a new worksheet named Summary that has the same layout as the monthly
worksheets. Position it to the left of the January worksheet. In each of the numeric
cells of the Summary worksheet enter a formula that uses cross sheet references to
sum up the values from the monthly worksheets.
You must write a formula in the upper left cell (C7) that you can copy down and across
to all the cells of the table. Your formula should use the SUM function.
DO NOT write a function that looks like this: SUM(MonthX!C7, MonthY!C7,… or like
this SUM(MonthX!C7+ MonthY!C7,… 5. [5] Format all the monthly and the summary worksheets in a neat and professional
manner. The monthly worksheets should all be identically formatted. Format the
Summary worksheet differently from the monthlies – for example different fonts or
colors.
This workbook is going to be presented to your boss’s boss so it can’t look like a hack
job. Tip: Use the theme styles on the Home ribbon to give complementary colors to the
headers, titles and so on. Put borders around all the cells in the tables. With numbers
this big no one cares about pennies, so don’t show them. 6. [4] Note the complex formula in merged cell B3 in each of the monthly worksheets. This
formula displays the name of the worksheet using the CELL and MID functions, neither
of which we’ve studied. The CELL function can return a variety of information about the
cell specified in the second argument.
Once you’re in the workplace, a very valuable skill will be to figure out on your own how
to use features of Excel that you’re not familiar with. Do some research on the CELL
function.
Tip: When you’re researching an Excel feature, always start your search with
the word “excel”. For example, Google “excel cell function”. Putting “excel”
first will help Google narrow the results.
Then in cells B4 and B5 on the Cell and Mid worksheet use the CELL function to display
information about cell B3. In cells C4 and C5 write one sentence that explains what the
function you wrote does. Display different types of information in B4 and B5 and don’t
use “filename” as the first argument, since you can see that on the monthly worksheet. 7. [2] 2|Page In cell B9 of the Cell and Mid worksheet write a MID function that displays the last four
words from the sentence in C9. Professor Shoemaker CSC 141 - Assignment 1.2- Cross Worksheet
8. [1] Color the monthly worksheet tabs in one color, the Summary worksheet in another.
Arrange them so they are like beloward it that way.
25 3|Page Total Professor Shoemaker
CSC 141 - Assignment 1.2- Cross Worksheet
Spring, 2017 Your Name
Date
List the full names people you collaborated with on this
assignment. If no one then say that. Spring, 2017 CELL Function MID Function Explanation Sentence
These are the times that try men's souls. Acme Widgets Sales
January 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
155
356
409
689
133
1929 933877
699963
1342771
322528
248161
3440770 21289
23619
22246
19362
20187
101477 Acme Widgets Sales
February 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
195
403
611
123
279
2662 Amount
1086522
1144554
275953
583810
785749
4829467 15221
17497
26457
14379
17499
97091 Acme Widgets Sales
March 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
705
383
306
331
737
2842 318500
327003
1115253
1259112
1223127
2255975 14543
24127
15658
21978
22870
96104 Acme Widgets Sales
April 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
732
583
634
408
433
2731 Amount
1121334
1271335
1046810
991797
334797
4938318 15876
17487
20525
26791
22335
82674 Acme Widgets Sales
May 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
639
202
154
758
326
2748 298217
721295
172457
768262
402382
3187103 22561
25286
24658
27259
17398
107496 Acme Widgets Sales
June 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
560
853
128
808
781
2603 841519
207594
774180
278340
532502
4297672 25744
25944
24452
16544
17709
111413 Acme Widgets Sales
July 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
363
294
761
405
662
2341 Amount
1131402
551478
746674
1260471
340494
3427941 26472
24762
22444
14921
20003
103766 Acme Widgets Sales
August 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
377
556
529
220
606
2518 405297
160434
1189580
620707
1273119
2558788 21323
26387
26598
27130
14994
111167 Acme Widgets Sales
September 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
799
515
123
549
777
2515 732080
590425
1309999
1376827
330669
4489137 14006
15264
18109
14010
15198
105911 Acme Widgets Sales
October 2016
Model
5D Mark IV
6D II
1DX
EOS Elan
10D
Totals Sales
Units Freight
Amount
580
404
504
365
425
2108 622897
607204
743372
856760
498832
4195486 17903
21462
20830
14925
25234
106624
Attachments: