Advanced Excel formula exercises
This path is for people who are comfortable with conditional sums and basic lookups and want the tools behind real reports: two-way lookups, rates that depend on a band, weighted averages, counts of distinct customers, and the finance functions behind loans, savings and investments.
The tasks are longer and the formulas combine several functions, so reading the problem carefully matters as much as the syntax. Each is checked on other data too, which is what tells a formula that works from one that happens to match.
What you will be able to do
- Combine INDEX and MATCH for a lookup in either direction, a two-way table, and the person with the highest value.
- Place a number in a band, such as a weight range, with XMATCH and next-smaller matching.
- Calculate a weighted average with SUMPRODUCT.
- Count distinct customers with UNIQUE, FILTER and ROWS.
- Sum values inside a window of dates with SUMIFS.
- Work out a loan payment with PMT, a savings balance with FV and the value of a project with NPV.
Skills covered
- Finance
- INDEX
- Absolute references
- MATCH
- Approximate match
- AVERAGE
- Banded lookup
- Business analysis
- Compound interest
- Concatenation
- COUNTIFS
- Date criteria
- Discounting
- Distinct count
Common mistakes to watch for
- Counting orders instead of customers
- A customer with several orders appears several times. Remove the repeats first, then count what is left.
- Using AVERAGE for a weighted average
- A plain average gives every row the same weight. When quantities differ, multiply each value by its weight, add the results and divide by the total weight.
- Mixing annual and monthly units
- A payment or a balance depends on the number of periods and the rate per period. An annual rate with monthly periods gives a result that is far off.
- Matching exactly when you need a band
- A weight or an amount rarely equals a limit exactly. Banded lookups ask for the next smaller value, not an exact match.
The exercises, in order
Look Up a Shipping Rate with INDEX and MATCH
Combine INDEX with two MATCH functions to read one cell of a table from a row label and a column label.
Sales Between Two Dates with SUMIFS
Build comparison criteria from cell values with &, so a report window can change without rewriting the formulas.
Weighted Average Price with SUMPRODUCT
Multiply two columns and add the results in one formula with SUMPRODUCT, and use it to compute a weighted average.
Who Sold the Most? INDEX, MATCH and MAX
Find the largest, second-largest or smallest value, then use MATCH and INDEX to return the label that belongs to it.
Shipping Rate Bands with INDEX and XMATCH
Look a number up in a table of bands by finding the largest lower limit that does not exceed it.
Count Distinct Customers with UNIQUE and FILTER
Remove duplicates with UNIQUE, filter a list with FILTER, and count the result with ROWS to get a single number.
Monthly Loan Payment and Total Interest with PMT
Use PMT with matching rate and period units, and build the total cost of a loan from the monthly payment.
Savings Goal: Future Value with FV
Use FV with a regular payment and a starting balance, matching the rate and period units, and separate deposits from interest.
Is the Project Worth It? Net Present Value with NPV
Discount future cash flows with NPV, treat the initial investment correctly, and turn the result into a decision.