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