Here I present another video solution to Computer Operator Practical Question paper Set 8 Question No 3. The question asked to create a table in Excel and asks us to perform various tasks:
- Apply Data validation so that users can type exactly 7 characters only in Symbol No column.
- Apply Conditional formatting in such a way that any of the the marks obtained is less than 50, it will be displayed in red color.
- Calculate Percent column for given marks of 4 subjects
- Calculate Grade as A+, A, B or C based on Percent is greater than 90, 60, 50 or less than 50. It should also award Grade C for those students who scored less than 60 marks in any of those 4 subjects.
I’v solved this question in Excel 2007. Watch the video below:
Update: I read the conditions for grade wrong and assumed the grade A+ is to be awarded for those who have percent > 90. In fact it had to check if any of the marks is greater than 90. So, you’ll have to adjust the formula in video a bit. Replace the portion of formula that calculates Grade A+ with following.
if(MAX(C2:F2)>90,”A+”
https://www.youtube.com/watch?v=3nODcSJmJps
Calculating Grade can be tricky because of the last clause added in the criteria. All those students who scored less than 60 marks in any of the subjects are to be awarded Grade C. If you used nested IF function and go on awarding sequentially, you’ll find your formula is not returning correct values. Take a note on that!
References:
- Data Validation: http://office.microsoft.com/en-001/excel-help/apply-data-validation-to-cells-HP010072600.aspx
- Conditional Formatting: http://www.wikihow.com/Apply-Conditional-Formatting-in-Excel
Leave a Reply