EXCEL PRACTICAL ASSIGNMENT FOR RSCIT STUDENTS
EXCEL PRACTICAL ASSIGNMENT FOR RSCIT STUDENTS
Assignment 1
Type the following data in excel worksheet and save it as first.xls
Type the following data in excel worksheet and save it as first.xls
A B C D E
513
501
504
513
511
516
532
504
432
501
510
517
479
494
498
Do the following
(a) Highlight column A and copy it to column C
(b) Sort the data in column C in ascending order
(c) What is the lowest number in the list (use a function)
(d) Copy the data in column A to column E and sort it in descending order
(e) What is the highest number in the list (use a function)
(f) How many numbers in this list are bigger than 500 (use a database function)
(g) How many numbers in column A are between 520 and 540 inclusive
(use a database function)
Assignment 2.
Type the following data in excel worksheet and save it as second.xls.
A B C D
People per physician Life Expectancy
X Y X * Y
370 70.5
6166 53.5
684 65
449 76.5
643 70
1551 71
616 60.5
403 51.5
Do the following
(a) Complete column C for finding product x * y
(b) Find sum of x column at the end of data
(c) Find sum of y column at the end of data
(d) Find sum of x * y column at the end of data
(e) Find sum of x^2
(f) Find sum of y^2
Assignment 3.
Enter the following data and save it in grade .xls
Name Marks1 Marks2 Marks3 Total Percentage Grade
Amit 80 70 80
Renu 70 60 90
Rajeev 60 50 80
Manish 50 30 90
Sanjeev 40 40 80
Anita 70 70 90
Do the following
(a) Compute the total marks and percentage of each student by entering appropriate formula.
(b) Compute the grades based on following criteria
If percentage >= 90 then grade = A
If percentage >= 80 and <90 then grade = B
If percentage >= 70 and <80 then grade = C
If percentage >= 60 and <70 then grade = D
If percentage < 60 then grade = E
Assignment 4.
Using grade.xls to perform the following formatting operations
(a) Draw a border around the worksheet
(b) Change the font size of heading to 14 points and underline it and hide column c
(c) Increase the width of column A to 15 characters
(d) Right Align the values in column B, C, F
Assignment 5.
A university maintains a year wise result for four courses and then generates an average report as given below
Sr no. Year Course1 Course2 Course3 Course4 Average
1 2002 356 300 300 400
2 2003 200 400 200 450
3 2004 256 500 400 600
4 2005 400 600 500 550
5 2006 456 450 550 450
6 Total
(a) Complete the report to calculate the course wise average in row 6
(b) Provide formula to calculate year wise average in column G
(c) Generate a column chart to compare data
Assignment 6.
A person wants to start a business and he has four schemes to invest money according to profit and years. Find out which scheme is the most profitable.
Investment Amount Percentage for Profit No of years
20000 10% 6 years
40000 20% 5 years
14000 30% 4 years
12000 15% 5 years
Assignment 7.
A company records the details of total sales (in Rs. ) sector wise and month wise in the following format
Jan Feb March April
Sector 30 12000 17000 14000 15000
Sector 22 14000 18000 15000 16000
Sector 23 15000 19000 16000 17000
Sector 15 16000 12000 17000 18000
(a) Enter the data in a worksheet and save it as sector.xls
(b) Using appropriate formula, calculate total sale for each sector
(c) Create a 3-D column chart to show sector wise data for all four months
(d) Create a 3-D pie chart to show sales in Jan in all sectors
practical
In column E, you have the average result for each students for an exam. In column F, write a function which returns if students Pass or Fail if the average mark is greater or equal than 10.
Change your function to display
· Fail (when the average is below 10)
· Pass (when the average is between 10 and 12)
· Good (when the average is between 12 and 14)
· Very Good (when the average is between 14 and 16)
· Excellent (when the average is beyond 16)
| Course 1 | Course 2 | Course 3 | Average | Result | |
| Luc | 8 | 15 | 9 | 10.67 | Fill your formula here |
| Estelle | 4 | 15 | 16 | 11.67 | |
| Laurent | 11 | 6 | 8 | 8.33 | |
| Paul | 17 | 16 | 3 | 12.00 | |
| Léa | 17 | 18 | 10 | 15.00 | |
| Murielle | 6 | 5 | 13 | 8.00 | |
| Thierry | 18 | 19 | 15 | 17.33 | |
Exercise : Management of the stocks
You manage inventory and orders of a company. You can not honor orders if you do not have the full amount requested.
So you create an IF function that will check that you have the quantity in your stock. If your stock is lower, you can not deliver the amount you have.
For example you can write the following function:
=IF(C3<B3,C3,B3)
For the second part of the exercise, you initiate an order with your suppliers if you could not fulfill the entire order. You can write the formula in different ways for an equivalent result
=IF(D4<B4,B4-D4,"")
or
=IF(D4=C4, B4<D4,"")
| | | |||||
| Item | Qty order | Qty in stock | Qty delivered | Qty to delivered(ans) | | |
| Smartphone | 50 | 45 | | 45 | | |
| Ipad | 37 | 51 | | 37 | | |
| USB Stick 4Go | 12 | 15 | | 12 | | |
| USB Stick 8Go | 75 | 47 | | 47 | | |
| USB Stick 16Go | 8 | 10 | | 8 | | |
| | | |||||
| | | |||||
Exercise : Equity portfolio
You have a stock portfolio. In column C you have the purchase price and column D the last price. Write the result in column E if you win money or in column F if you are losing. The result should also take into account the number of shares you own.
Then you make the sum of columns E and F and then in cell E9, you make the difference in cell E8 and F8 to see if you gain or loss.
(Solution in the other tab in the workbook)
| Portfolio | ||||||
| Stock | Volume | Buy | Last | Gain | Lose | |
| AAA | 25 | 35.4 | 45.8 | | | |
| BBB | 53 | 42.8 | 37.5 | | | |
| CCC | 50 | 86.1 | 88 | | | |
| DDD | 75 | 75 | 69 | | | |
| EEE | 100 | 8 | 10 | | | |
| | | | ||||
| Total | | |||||
| solution | ||||||
| Portfolio | ||||||
| Stock | Volume | Buy | Last | Gain | Lose | |
| AAA | 25 | 35.4 | 45.8 | 260 | 0 | |
| BBB | 53 | 42.8 | 37.5 | 0 | 280.9 | |
| CCC | 50 | 86.1 | 88 | 95 | 0 | |
| DDD | 75 | 75 | 69 | 0 | 450 | |
| EEE | 100 | 8 | 10 | 200 | 0 | |
| | 555 | 730.9 | | |||
| Total | -175.9 | |||||
No comments:
Post a Comment