Assignment

Practice Questions
Exercise 1: Prepare the following worksheet. 10 Marks
(i). Enter the sample data in columns 1, 2, 3, for at least 5 employees /

rows.
(ii). Apply formulas to calculate Hours worked as “Time Out – Time
In”
(iii). Use the formula to compute Total Amount/Pay as Wages*Hours
Worked
Employee Time Hours Total

Time In Wages
Name out Worked Amount/Pay
Formula $12.00 Formula here
Ali 8:30 5:30
here
Ahmed 9:00 4:45 $10.50
Usman 7:52 6:00 $13.75
Exercise 2: The following table shows the subject-wise marks of 5
students in a school. Since the results pertain to the annual
examinations, the maximum marks of every subject are 100.
10 Marks
We have to calculate the percentage of marks for all students.
Student English Physics Chemistry Biology Maths Formula

Here
Ali 67 39 59 84 80
Nimra 75 79 92 68 38
Haider 44 41 60 38 71
Saad 56 77 77 87 75
Alina 67 72 65 69 83
]
Exercise 3: Prepare the following worksheet. 10 Marks
(i). Enter the sample data in columns 2, 3, 4 and 5 for at least 10
candidates.
(ii). Apply formulas to calculate Total Score
(iii). Apply formula for Status according to criteria;
Get Admission if total score > 50, otherwise ineligible.
Entry Admission Interview Academics Total

Test Test Score
Sr.No Status
Roll Score(out (Out of (Out of (Out of
No of 50) 10) 40) 100)
1. 12 20 8 33 score Formula
2. 15 45 9 32
3. 40 25 6 37
4. 50 32 5 25
Exercise 4: Objectives:
 Introduction to MS Excel files, Workbooks, Worksheets, Columns
and Rows.
 Formatting Worksheets.
 AutoFill, Numeric formats, previewing
worksheets.
 10. Calculate the Gross Pay for employee;

 11. Calculate the Social Security Tax (S.S Tax), which is 6% of the
Gross Pay
 12. Calculate the Net Pay
 13. Set the work sheet vertically and horizontally on the page.
 14. Save your work.
Exercise: 5 Objectives:
 Using Formulas.
 Header and Footers.
 1. Open a new workbook and save the file with the name “Call
Statistics”.
 2. Delete Sheet 2 & 3, and rename Sheet 1 to (Call Statistics).
 3. Enter the labels and values in the exact cells locations as desired.
 4. Set the row height of rows 1 & 3 to size 30; and rows 4 until 10 to
size 20.
 5. Set labels alignment appropriately.
 6. Use Warp Text, Orientation and merge cells as desired.
 7. Apply border, gridlines and shading to the table as desired.
 8. Format column E to include euro (€) sign with two decimal places.
 9. Format cell B12 to include % sign with 0 Decimal places.
 10. Calculate the Calls per Hour, enter a formula in cell D4 to divide
numbers of calls by Hours worked. Using AutoFill, copy the formula to
the remaining cells.
 11. Calculate the Bonus.
 12. Calculate the ‘TOTAL.
 13. Set the worksheet vertically and horizontally on the page.
 14. Create a header that includes your name in the left section, and
your ID number in the right section. Create the footer that includes the
current Date in the center.
Exercise 6
 Number, Commas and Decimal numeric formats.
 Working with Formulas (Maximum, Minimum, Average, Count and
Sum).
 Percentage Numeric Formats.
i. Enter the formula to find COMMISSION for the first employee.
ii. The commission rate is 2% of sales,
iii. Enter the formula to find TOTAL SALARY for the first employee where:
iv. Enter formula to find TOTALS, AVERAGE, HIGHEST, LOWEST, and
COUNT values. Copy the formula to each column.
Exercise 7
 Working with the IF
Statement.
For the above table find the following:

 1. TAX (If ITEM PRICE is less than 100, TAX is 50, otherwise it
should be 100).
 2. Find TOTAL PRICE BEFORE TAX
 3. Find TOTAL PRICE AFTER TAX
 4. Find RATE (If TOTAL PRICE AFTER TAX > 3500 then the rate
is “HIGH”, otherwise it is REASONABLE.
 5. Find Count of Items, Average of Taxes, Min Item PRICE and Max
Item PRICE.
 
Exercise 8
Objectives:
  Working with Sum IF and Count IF statements.
  Inserting Charts.
 1. Create the worksheet shown above.

 2. Set the Text alignment, Columns width and high appropriately.
 3. Use AutoFill to put the Series Numbers into cells A5:A7.
 4. Format cells C3:G7, C8:E11, C13:E13 to include dollar sign with
two decimal places.
 5. Find the Average Sales and Maximum Sales for each City.
 6. Find the Total Sales for each Month.
 7. Calculate the Profit for each month
 8. Calculate the 10% Bonus, which is 10% of the Profit.
 9. Find the Total Sales for each Month; only for sales greater than
30,000.
 10. Find the No of Sales for each Month; only for sales greater than
30,000.
 11. Create the following Charts:
Exercise 9
Objectives:
  Working with Sum IF and Count IF statements.
  Inserting Charts.9\k
,
 1. Open a new workbook and create the above worksheet.

 2. Make sure that your worksheet looks like the picture (Alignment,
Shedding, Borders, Wrap text, Orientation …).
 3. Find the entire customer IDs.
 4. Format Colum E & D to Currency with dollar sign and two
decimal places.
 5. Find the Total Annual Purchases for each City.
 6. Find the Average Annual Purchases for each Education.
 7. Find the total number of customers from each gender.
 8. Find the total annual salary for each gender in each city.
 9. Create the following Chart:
Exercise 10
Example to Demonstrate Classic Nested IF Formula
As an example, let's find out the categorized list of students based on their
exam marks. We have the following classification:
Classification Marks
Excellent 90-100
Good 80-90
Average 60-80
Below Average 40-60
Poor Below 40
With nested IF statements, it's essential to arrange the logical conditions in

an accurate order, i.e., either high to low or low to high. In the above
example, we order the "highest" value first, then the "second highest", and
so on:
Exercise 11
Supposing you have a list of students in column A and their exam scores in column B,
and you want to classify the scores with the following conditions:
 Excellent: Over 249

 Good: between 249 and 200, inclusive
 Satisfactory: between 199 and 150, inclusive
 Poor: Under 150
Exercise 12
As an example, let's find out commissions for a number of sellers based on the amount
of sales they've made:
Commission Sales
3% $1 - $50
5% $51 - $100
7% $101 - $150
10% Over $150
As an example, let's find out commissions for a number of sellers based on the amount of sales
they've made
In math, changing the order of addends does not change the sum. In Excel, changing the order of IF
functions changes the result. Why? Because a nested IF formula returns a value corresponding to
the first TRUE condition. Therefore, in your nested IF statements, it's very important to arrange the
conditions in the right direction - high to low or low to high, depending on your formula's logic. In our
case, we check the "highest" condition first, then the "second highest", and so on:
f we placed the conditions in the reverse order, from the bottom up, the results would be all wrong
because our formula would stop after the first logical test (B2>=1) for any value greater than 1. Let's
say, we have $100 in sales - it is greater than 1, so the formula would not check other conditions and
return 3% as the result.
/
Exercise 13
the unit price varies depending on the specified quantity, and your goal is to write a formula that
calculates the total price for any amount of items input in a specific cell.
Unit Price per

Quantity unit
1 to 10 $20
11 to 19 $18
20 to 49 $16
50 to 100 $13
Over 101 $12
This task can also be accomplished by using multiple IF functions. The logic is the same
as in the above example, the only difference is that you multiply the specified quantity
by the value returned by nested IFs (i.e. the corresponding price per unit).
Assuming the user enters the quantity in cell B8, the formula is as follows:

Assignment

Uploaded by

Copyright:

Available Formats

Assignment

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignment

Uploaded by

Copyright:

Available Formats

Practice Questions

Exercise 1: Prepare the following worksheet. 10 Marks

(i). Enter the sample data in columns 1, 2, 3, for at least 5 employees /

Employee Time Hours Total

Student English Physics Chemistry Biology Maths Formula

Entry Admission Interview Academics Total

 10. Calculate the Gross Pay for employee;

For the above table find the following:

 1. Create the worksheet shown above.

 1. Open a new workbook and create the above worksheet.

Below Average 40-60

With nested IF statements, it's essential to arrange the logical conditions in

 Excellent: Over 249

10% Over $150

Unit Price per

Over 101 $12

You might also like