** **

**
Instruction for Excel**

**We have talked about the calculation of Total, Average
(using equal distribution of percentage) and Average (different weight for
different subjects), and bar graphs in class. Here are the reminders for the
ranking, category, and pie charts. **

**The cell numbers I give here are
examples. You need to refer to your own table for the cell numbers:**

** **

**
1.
For ranking**

** **

The formula you need to use is RANK

Number = I4 (à the cell you are looking at)

Ref = $I$4:$I$13 (à the ten cells you want to compare their averages scores)

Order = 0 (à this means the decreasing; the lowest number is for the highest score, e.g. Rank 1 is for highest grade)

**
2.
To assign a letter to the “category”**

** **

The formula you need to use is VLOOKUP

Value = I4 (àthe cell of average)

Table_array = $A$18: $B$27 (à the area where your table of 甲乙丙丁 is)

Col_index_number = 2 (à this is the second column counting from the A column, where甲乙丙丁 is shown)

Range_lookup = TRUE

**
3.
To draw your pie chart**

**For this, you need to count how many
甲乙丙丁 people
there are in the ten cells**

** **

The formula you need to use is COUNTIF

Range = $K$4:$K$13 (à the ten cells you are going to count the number of 甲乙丙丁)

Criteria = “甲” or “乙” or “丙”

Email: Doris Shih