Rank and percentile rank by department

Rank and percentile rank are often used together in performance reporting. Rank shows position (1 = highest), while percentile rank shows relative standing from 0 to 1.

PERCENTRANK.INC calculates the rank of a value as a fraction of the dataset. The "INC" means the range is inclusive of 0 and 1 (so the top score returns exactly 1, and the lowest returns exactly 0).

The syntax for percentile rank is:

=PERCENTRANK.INC(array, x, [significance])

• array: the full set of values to rank against
• x: the specific value you want to rank
• significance (optional): number of decimal places (defaults to 3)

The syntax for rank is:

=RANK.EQ(number, ref, [order])

• number: the value to rank
• ref: the range to rank against
• order: 0 for descending rank (highest value gets rank 1)

We use RANK.EQ (instead of RANK) here because it is the explicit modern function name but using RANK would work just as well.

Your task

This spreadsheet has performance scores for the Engineering department (column B).

Fill in:

• Employee percentile rank (column C) and rank (column D)

• For employee rows, calculate rank and percentile rank within the department.

Make sure each formula can be filled down without breaking the reference to the full employee score range.