Gross margin by product
IntermediateGross margin is one of the most important profitability metrics in business. It tells you how much profit you make on each product after covering direct costs.
In this exercise, you'll analyze five products to find out which ones are the most profitable. You have revenue and cost data for each product, and you need to calculate:
- Gross margin (revenue minus cost)
- Margin % (gross margin divided by revenue)
- Rank based on margin % (highest margin = rank 1)
What you need to do
Step 1: Calculate gross margin
In column D, calculate the gross margin for each product by subtracting cost from revenue.
Step 2: Calculate margin percentage
In column E, calculate each product's margin as a percentage of its revenue.
Step 3: Rank the products
In column F, use the RANK function to rank each product based on its margin percentage. The product with the highest margin % should be ranked #1.
The RANK function syntax is:
=RANK(number, ref, [order])
- number: The value you want to rank
- ref: The range of values to rank against
- order: Optional. Use 0 or omit for descending (highest = 1), use 1 for ascending
When you copy the RANK formula down, think about which cell references need to stay fixed.
Need some help?
Hint 1
Gross margin = Revenue - Cost. Margin % = Gross margin ÷ Revenue.
Hint 2
For the RANK formula, the first argument is the cell you want to rank (like E2). The second argument is the full range to compare against.
Hint 3
When copying the RANK formula down, you need absolute references ($) on the range so it doesn't shift. For example: =RANK(E2,$E$2:$E$6)
Related function(s)
Gross margin by product
IntermediateGross margin is one of the most important profitability metrics in business. It tells you how much profit you make on each product after covering direct costs.
In this exercise, you'll analyze five products to find out which ones are the most profitable. You have revenue and cost data for each product, and you need to calculate:
- Gross margin (revenue minus cost)
- Margin % (gross margin divided by revenue)
- Rank based on margin % (highest margin = rank 1)
What you need to do
Step 1: Calculate gross margin
In column D, calculate the gross margin for each product by subtracting cost from revenue.
Step 2: Calculate margin percentage
In column E, calculate each product's margin as a percentage of its revenue.
Step 3: Rank the products
In column F, use the RANK function to rank each product based on its margin percentage. The product with the highest margin % should be ranked #1.
The RANK function syntax is:
=RANK(number, ref, [order])
- number: The value you want to rank
- ref: The range of values to rank against
- order: Optional. Use 0 or omit for descending (highest = 1), use 1 for ascending
When you copy the RANK formula down, think about which cell references need to stay fixed.
Need some help?
Hint 1
Gross margin = Revenue - Cost. Margin % = Gross margin ÷ Revenue.
Hint 2
For the RANK formula, the first argument is the cell you want to rank (like E2). The second argument is the full range to compare against.
Hint 3
When copying the RANK formula down, you need absolute references ($) on the range so it doesn't shift. For example: =RANK(E2,$E$2:$E$6)