Form 3 Excel

1
Form 3 Excel

• Assignment 6
• Instruction Guideline

2
A worksheet Performance
Assignment 6
3
To generate Grade randomly

• The following function can be used.
• CHOOSE(RAND( )101,"A","A","A-","B","B","B-","
  C","C","C-","D")
• choose( a number, option 1, option 2, ,,,, ..
  )
• A number RAND( ) 10 1
• It generates a number from 1 to 10
• Option 1 is A.
• Option 2 is A.
• Option 3 is A-.

4
(No Transcript)
5
Copy the function to all cells

• Formula copying
• Copy the formula from cell B3 to a range
• The range is from cell B3 to D22.

6
Create a transform table
7
(No Transcript)
8
(No Transcript)
9
Grade to Mark Conversion
10
Formula of conversion

• VLOOKUP(B3,TransformTable!A3B12,2,FALSE)
• VLOOKUP( cell to be converted,
• array to store the transform table,
• column number among the array to perform
  conversion,
• False exact comparison )

11
(No Transcript)
12
VLOOKUP(B3, Transform Table!A3B12, 2, FAL
SE)
13
(No Transcript)
14
Copy the marks from formula to value

• Copy the range to store the marks to a new
  worksheet.
• Get the sum of each student by AutoSum.

15
The total marks of each student
16
Copy the range of marks to a new worksheet

• Including the total,
• Sort the total into descending order
• Select menu item Data
• Choose Sort
• Choose with header
• Choose Totals with Descending

17
Add ranking to students
18
Formula to rank

• RANK(E3,E3E38)

19
RANK(E3,E3E38)