Key Take-Aways

- LARGE(array, n) returns the nth largest value; SMALL(array, n) returns the nth smallest.

- RANK(number, array) gives the ordinal rank of a value inside a range; PERCENTRANK(array, x) returns that rank as a percentage.

- Use absolute references for the array (for example $E$2:$E$4135) and relative references for the cell you are ranking so formulas fill down correctly.

- Practical Excel shortcuts: Ctrl+Shift+Down to select a contiguous column, F4 to fix (absolute) references, and double-click the fill handle to apply downward.

- PERCENTRANK is helpful when you want to compare values on a 0 to 100% scale; RANK is better for ordinal comparisons.

## What these functions do and when to use them

- LARGE and SMALL

- Use LARGE when you need the nth largest value from a list. Syntax: =LARGE(array, n).

- Use SMALL when you need the nth smallest value. Syntax: =SMALL(array, n).

- Example: =LARGE(A2:A8, 2) returns the second largest value in A2:A8. If the second largest number is 90, the formula returns 90.

- RANK

- Use RANK when you want the position of a specific value in a list. Syntax: =RANK(number, array, [order]). The order argument is optional: 0 or omitted means descending (largest = rank 1), 1 means ascending (smallest = rank 1).

- Example: =RANK(A2, A2:A8) will return 2 if A2 is the second largest value in the range.

- PERCENTRANK

- Use PERCENTRANK to express a value's relative position within a dataset on a 0 to 1 or 0% to 100% scale. Syntax: =PERCENTRANK(array, x[, significance]). Newer Excel offers PERCENTRANK.INC and PERCENTRANK.EXC for inclusive or exclusive behavior.

- Example: =PERCENTRANK(A2:A8, A2) might return 1 or 100% if A2 is the maximum value, and 0 or 0% if it is the minimum.

## Step-by-step examples from a salary dataset

1. Find the 10th highest salary

- Formula: =LARGE(salary_column, 10)

- In the video example: =LARGE(E:E, 10) returned 24,642,857 as the 10th highest salary.

2. Find the 100th lowest salary

- Formula: =SMALL(salary_column, 100)

- In the video example: =SMALL(E:E, 100) returned 405,000 as the 100th lowest value.

3. Calculate rank and percent rank for each salary in columns F and G

- Rank formula pattern:

- Put this in F2: =RANK(E2, $E$2:$E$4135)

- E2 is the number being ranked. $E$2:$E$4135 is the fixed array reference so it does not shift when filling down.

- Percent rank formula pattern:

- Put this in G2: =PERCENTRANK($E$2:$E$4135, E2)

- This returns a decimal between 0 and 1 by default. Format as Percentage to show 0% to 100%.

## Practical Excel techniques demonstrated

- Select a contiguous column quickly: click the first data cell, press Ctrl+Shift+Down to select to the last contiguous cell.

- Fix the reference for the array: after selecting the range press F4 to make it absolute. That yields a reference like $E$2:$E$4135 so the array stays the same when you copy the formula.

- Keep the number argument relative: the cell you are ranking (for example E2) should remain a relative reference so it updates as you fill down.

- Fill formulas down quickly: double-click the fill handle to copy the formula down the contiguous block of adjacent data.

## Notes about order, ties, and newer functions

- Ties: RANK gives the same rank to tied values. For example, two identical highest values will both return rank 1, and the next value may skip a rank.

- Order argument in RANK: supply 0 or omit to rank descending (largest = 1). Supply 1 to rank ascending (smallest = 1).

- PERCENTRANK variants: newer Excel versions include PERCENTRANK.INC and PERCENTRANK.EXC. PERCENTRANK.INC includes endpoints and returns 0 for min and 1 for max. Use the variant that matches your reporting needs.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Always lock the array reference when you intend to copy the formula. Forgetting to make the array absolute is the most common mistake and will produce incorrect ranks as you fill down.

- If your data contains blanks or text, ensure the array selection covers only numeric values or filter the data first. Non-numeric cells can cause errors or unexpected results.

- When comparing percent ranks across different datasets, be sure the ranges are consistent. PERCENTRANK is meaningful only within the context of the array you specify.

- If you need percentiles instead of percent ranks, use the PERCENTILE.EXC or PERCENTILE.INC functions depending on whether you want exclusive or inclusive behavior.

## FAQ

### How do I get the nth largest or nth smallest value in Excel?

Use =LARGE(range, n) for the nth largest and =SMALL(range, n) for the nth smallest. Example: =LARGE(A2:A100, 3) returns the third largest value.

### How does RANK differ from PERCENTRANK?

RANK returns the ordinal position of a value inside a list, for example rank 1, 2, or 3. PERCENTRANK returns the relative position as a percentage between 0 and 1 or 0% and 100%. Use RANK for ordering and PERCENTRANK when you want a normalized comparison.

### How do I lock the range when filling formulas down?

Select the range (for example E2 then Ctrl+Shift+Down), then press F4 to convert it to an absolute reference like $E$2:$E$4135. Keep the value cell relative (E2) so it updates as you copy down.

### What happens when there are ties in RANK?

Tied values get the same rank. The next rank will be incremented by the number of tied values, which can cause rank numbers to skip.

### Why is PERCENTRANK showing 100% or 0%?

PERCENTRANK returns 100% for the maximum value in the specified array and 0% for the minimum, assuming you use the inclusive variant. If you see 100% for your top value and 0% for your bottom value, the function is working as expected.

## Summary of Key Points / Take-Home Messages

- Use LARGE and SMALL to pull nth values from a list. Use RANK to get ordinal position and PERCENTRANK to get a relative percentage.

- Lock the array reference with F4 and keep the cell you are ranking relative so the formula can be filled down correctly.

- Use Ctrl+Shift+Down, F4, and the fill handle to write formulas efficiently across large datasets.

- Be mindful of ties and choose the correct PERCENTRANK variant for inclusive or exclusive behavior.