CS001 Assignment 3 Solution and Discussion

Computer Proficiency License
(CS001)
Assignment # 03
Total marks = 20Due Date
23th January 2020Please carefully read the following instructions before attempting assignment.
RULES FOR MARKING
It should be clear that your assignment would not get any credit if:
The assignment is submitted after the due date.
The submitted assignment does not open or file is corrupt.
Strict action will be taken if submitted solution is copied from any other student or from the internet.You should consult the recommended books to clarify your concepts as handouts are not sufficient.
You are supposed to submit your assignment in xls or xlsx format.
Any other formats like scan images, doc,docx,PDF, zip, rar, ppt and bmp etc will not be accepted.Topic Covered:
• Module 4 (Spreadsheets):
o Introduction to Microsoft Excel
o Saving an Excel File
o Creating Complex Formula
o Sorting DataNote: Create an excel file with two sheets, one for Question 1 and other for Question 2.
NOTE
No assignment will be accepted after the due date via email in any case (whether it is the case of load shedding or internet malfunctioning etc.). Hence refrain from uploading assignment in the last hour of deadline. It is recommended to upload solution file at least two days before its closing date.
If you people find any mistake or confusion in assignment (Question statement), please consult with your instructor before the deadline. After the deadline no queries will be entertained in this regard.
For any query, feel free to email at:
[email protected]Questions No 01 10 marks
Create a worksheet in excel with the name “Question 1” with the following data as shown below:
Then, perform the following tasks in the worksheet for the given data:
 Create a formula in the Quantity column to calculate the total quantity of each type of Paper Goods sold from September to January. In simple words,
Quantity = Total number of goods sold for each type from September to January  Create a formula in the Sales column to calculate the total price of quantity sold from September to January for each type of goods.
Total Price of Goods Sold = Quantity * Unit Price  In the row Grand Total, create a formula to calculate the total number of goods of all types sold per month.
Grand Total = Total number of goods sold for all paper types in each month  Create a new row with label Mod Sale Item under the row Grand Total. In Mod Sale Item row, for each respective cell of month column, create a formula to find the Maximum quantity of goods sold for any type. For example, in month of September, Toilet papers were sold the most which were 115. So, our formula in Mod Sale row should display 115. You should calculate the same for each month.
 Create a new row with label Mode Sale Goods at the end of the worksheet. In this row, you will create a formula to calculate the mode value for quantities of each goods type sold during a particular month and then displays the name of the goods representing the mode value.
Questions No 02 10 marks
Create a worksheet with the name “Question 2” with the following data as below:
Enter the formula to find the number of Computer science scholars in cell “G8” and number of Management scholars in cell “G9” respectively.

In cells “H8” and “H9”, enter formula to calculate the total pay for Computer science scholars and Management scholars.

In cells “I8” and “I9” calculate the average pay for Computer science scholars and Management scholars using the values given in Pay column (column D).

In cells “J8” and “J9”, calculate the number of Computer science scholars and Management scholars having pay more than 50,000.
Note: Create an excel file with two sheets, one for Question 1 and other for Question 2.
 Create a formula in the Quantity column to calculate the total quantity of each type of Paper Goods sold from September to January. In simple words,

Ideas Solution!