The most comprehensive part of the course — 57 formulas covering text, lookup, statistical, financial, and logic formulas used daily in Finance, Accounting, Data Analysis, and FP&A roles.
This is Part 3 — the final part of the 3-part Professional Excel Training Course:
| 📗 Part 1 | 📘 Part 2 | 📙 Part 3 — You Are Here |
|---|---|---|
| Navigation & Formatting | Functions | Formulas |
| 14 Topics | 7 Topics | 57 Formulas |
| 🔗 Open Part 1 | 🔗 Open Part 2 | ✅ Current |
Part 3 is the most extensive part of the series. It covers 57 real-world formulas across 6 categories — every formula includes a use case, syntax explanation, and a real worked example from Finance, HR, Sales, or Accounting.
📎 The Excel file is attached directly in this repository.
Click on
Excel_Course_Part-3_Formula.xlsxabove to download it.
- Open in Microsoft Excel 2016 or later
- Enable editing if prompted
| # | 🔗 Formula | ⚡ What It Does |
|---|---|---|
| 01 | UPPER, LOWER, PROPER | Change text to ALL CAPS, lowercase, or Title Case |
| 02 | TEXTJOIN | Join multiple cells together using a delimiter |
| 03 | LEFT, RIGHT | Extract characters from the left or right of text |
| 04 | TRIM | Remove all extra/unnecessary spaces from text |
| 05 | FIXED | Format numbers as text with controlled decimals |
| 06 | FIND | Find the position of specific text inside a cell |
| 07 | FORMULATEXT | Display a formula as readable text in a cell |
| # | 🔗 Formula | ⚡ What It Does |
|---|---|---|
| 08 | SUMIF | Sum values that match one condition |
| 09 | COUNTIF | Count cells that match one condition |
| 10 | AVERAGEIF | Average values that match one condition |
| 11 | SUMIFS | Sum values matching multiple conditions |
| 12 | COUNTIFS | Count cells matching multiple conditions |
| 13 | AVERAGEIFS | Average values matching multiple conditions |
| 14 | SUMPRODUCT | Multiply arrays then sum — powerful alternative to SUMIFS |
| 15 | ABS | Return the absolute value — remove negative signs |
| 16 | ROUND, ROUNDUP, ROUNDDOWN | Round numbers to any decimal or whole number |
| 17 | COUNTBLANK | Count empty/blank cells in a range |
| 18 | RAND | Generate random decimals between 0 and 1 |
| 19 | RANDBETWEEN | Generate random whole numbers between two values |
| 20 | TRANSPOSE | Flip data from vertical to horizontal (or vice versa) |
| # | 🔗 Formula | ⚡ What It Does |
|---|---|---|
| 21 | VLOOKUP | Look up a value vertically and return another column |
| 22 | HLOOKUP | Look up a value horizontally and return another row |
| 23 | INDEX | Return a value from a table by row and column number |
| 24 | MATCH | Find the position of a value inside a range |
| 25 | OFFSET | Return a range offset from a reference cell |
| 26 | COLUMN | Return the column number of a cell |
| 27 | ROW | Return the row number of a cell |
| 28 | COLUMNS | Count the number of columns in a range |
| 29 | ROWS | Count the number of rows in a range |
| 30 | CHOOSE | Return a value from a list based on an index number |
| # | 🔗 Formula | ⚡ What It Does |
|---|---|---|
| 31 | MIN, MAX, MEDIAN | Find smallest, largest, and middle value |
| 32 | MINIFS, MAXIFS | Find min/max with conditions applied |
| 33 | RANK | Rank numbers from highest to lowest or vice versa |
| 34 | CORREL | Find the correlation between two data sets |
| 35 | SLOPE | Calculate the slope of a linear regression line |
| 36 | FORECAST | Predict future values based on historical data |
| 37 | FREQUENCY | Count how many values fall into defined ranges |
| 38 | ISNUMBER | Check whether a cell contains a number |
| 39 | DMIN | Find min value in a database matching criteria |
| 40 | DMAX | Find max value in a database matching criteria |
| # | 🔗 Formula | ⚡ What It Does |
|---|---|---|
| 41 | IF | Return different results based on a true/false condition |
| 42 | IF(AND) | Return result only if ALL conditions are met |
| 43 | IF(OR) | Return result if ANY one condition is met |
| # | 🔗 Formula | ⚡ What It Does |
|---|---|---|
| 44 | NPV | Net Present Value of future cash flows |
| 45 | IRR | Internal Rate of Return on an investment |
| 46 | PMT | Monthly/annual loan payment amount |
| 47 | PPMT | Principal portion of a loan payment |
| 48 | IPMT | Interest portion of a loan payment |
| 49 | EFFECT | Effective annual interest rate with compounding |
| 50 | DB | Depreciation using declining balance method |
| 51 | RATE | Interest rate per period of an annuity |
| 52 | PV | Present value of future cash flows |
| 53 | FV | Future value of an investment |
Change text case — capitalise, lowercase, or title-case an entire column instantly
| Formula | What It Does | Example Input | Result |
|---|---|---|---|
=UPPER(cell) |
ALL CAPITALS | sedan |
SEDAN |
=LOWER(cell) |
all lowercase | SEDAN |
sedan |
=PROPER(cell) |
First Letter Capital | alabama |
Alabama |
Real Example — Car Dealership Data Cleanup:
A Financial Analyst exported data from the company ERP system. Product descriptions came in ALL CAPS and branch locations came in all lowercase. Before the management meeting, they needed to fix both.
=LOWER(C34) → converts "SEDAN" to "sedan"
=PROPER(D34) → converts "alabama" to "Alabama"
| Product (From ERP) | Branch (From ERP) | Product (LOWER) | Branch (PROPER) |
|---|---|---|---|
| SEDAN | alabama | sedan | Alabama |
| SPORTS UTILITY VEHICLE (SUV) | california | sports utility vehicle (suv) | California |
| HATCHBACK | florida | hatchback | Florida |
Join multiple cells together using a separator — far faster than manual concatenation
Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
When to use: Creating Receipt IDs, codes, labels, or any combined field from multiple columns.
Example #1 — Receipt ID Generator:
| Car Brand | Date of Sale | Car Model ID | Formula | Result |
|---|---|---|---|---|
| Volkswagen | 2019 | CA143 | =TEXTJOIN("-",TRUE,C37,YEAR(D37),E37) |
Volkswagen-2019-CA143 |
| Porsche | 2019 | CA438 | Porsche-2019-CA438 |
|
| BMW | 2019 | CA348 | BMW-2019-CA348 |
✅ TEXTJOIN only requires the delimiter once, while manual
&"-"&concatenation requires it between every single cell — TEXTJOIN is far more scalable for large datasets.
Example #2 — Sales Rep by Car Brand:
=TEXTJOIN(", ", TRUE, IF($D$59:$F$67=C89, $D$58:$F$58, ""))
→ Returns: "John Markus, Henry Davidson" (reps who sold Toyota)
Extract a specific number of characters from the left or right side of any text
Syntax:
=LEFT(text, num_chars) ← Extract from left
=RIGHT(text, num_chars) ← Extract from right
Basic Examples:
| Text | Formula | Result |
|---|---|---|
| Chris Alexander | =LEFT(A1, 1) |
C |
| Chris Alexander | =LEFT(A1, 3) |
Chr |
| Chris Alexander | =RIGHT(A1, 1) |
r |
| Matthew | =RIGHT(A1, 6) |
atthew |
Advanced — Extract First and Last Name:
First Name: =LEFT(A1, FIND(" ", A1) - 1)
Last Name: =RIGHT(A1, LEN(A1) - FIND(" ", A1))
| Full Name | First Name | Last Name |
|---|---|---|
| Chris Alexander | Chris | Alexander |
| Matthew Simpson | Matthew | Simpson |
| Alannah Johnson | Alannah | Johnson |
Remove all extra spaces from text — essential for cleaning exported data
Syntax: =TRIM(text)
| Text with Extra Spaces | After TRIM |
|---|---|
My name is Nazmul. |
My name is Nazmul. |
I live in Bangladesh. |
I live in Bangladesh. |
I am 25 years old. |
I am 25 years old. |
I am a student of ICAB. |
I am a student of ICAB. |
💡 TRIM is one of the first formulas to apply when cleaning raw data exported from any ERP or database system.
Format a number as text with controlled decimal places and commas
Syntax: =FIXED(number, decimals, no_commas)
| Value | Formula | Result | What It Does |
|---|---|---|---|
| 23234.23455 | =FIXED(A1, 2) |
23,234.23 | 2 decimals with comma |
| 1123.21 | =FIXED(A1, 0) |
1,123 | No decimal with comma |
| 678900.55 | =FIXED(A1, 0, TRUE) |
678901 | No decimal, no comma |
| 2345.6789 | =FIXED(A1, 3, FALSE) |
2,345.679 | 3 decimals with comma |
Find the exact position of specific text inside a cell — case sensitive
Syntax: =FIND(find_text, within_text, [start_num])
Example #1 — Basic Position Lookup:
| Find | Within | Start | Result |
|---|---|---|---|
| "are" | "How are you?" | 5 | 5 — "are" starts at position 5 |
| "in" | "I live in Dhaka in Bangladesh." | 1 | 8 — first "in" at position 8 |
| "in" | "I live in Dhaka in Bangladesh." | 9 | 17 — second "in" found at position 17 |
⚠️ FIND is case-sensitive. Searching for"e"in"Example"returns 7 (the last letter), NOT 1, because the capital"E"at position 1 does not match.
Practical use: Combined with LEFT and RIGHT to extract names, codes, or IDs.
Display any formula as readable text — great for documentation and presentations
Syntax: =FORMULATEXT(reference)
Real Example — HR Compensation Sheet:
A Financial Analyst needed to print a compensation model for a management meeting and explain how each salary was calculated. Using FORMULATEXT, they placed the formula text next to the results column.
| Name | Basic Salary | Total Compensation | Formula (FORMULATEXT) |
|---|---|---|---|
| Sabbir | 10,000 | 17,750 | =(D29+E29+F29+G29-H29-I29-J29)*(1+L29) |
| Shafin | 11,000 | 20,306 | =(D30+E30+F30+G30-H30-I30-J30)*(1+L30) |
Sum values in a range that match ONE condition
Syntax: =SUMIF(range, criteria, sum_range)
Real Example — Car Dealership Sales by Branch:
| Formula | Branch | Result |
|---|---|---|
=SUMIF(D:D, "California", E:E) |
California | $250,750 |
✅ The General Manager wanted total California sales for the year. SUMIF scanned the Branch Location column for "California" and summed the Sales Revenue column.
Count the number of cells that match ONE condition
Syntax: =COUNTIF(range, criteria)
Real Example:
| Formula | Branch | Result |
|---|---|---|
=COUNTIF(D:D, "California") |
California | 20 cars sold |
Calculate the average of values that match ONE condition
Syntax: =AVERAGEIF(range, criteria, average_range)
Real Example:
| Formula | Branch | Result |
|---|---|---|
=AVERAGEIF(D:D, "California", E:E) |
California | $12,537.50 average |
Sum values matching MULTIPLE conditions simultaneously
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Real Example — Sales by Branch AND Product Type:
| Branch | Product | Total Sales |
|---|---|---|
| Alabama | Sedan | $41,651 |
| California | Sports Utility Vehicle | $20,500 |
| Florida | Hatchback | $60,586 |
| Washington | Convertibles | $5,500 |
=SUMIFS(E:E, D:D, "Alabama", C:C, "Sedan") → $41,651
Count cells matching MULTIPLE conditions simultaneously
Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)
Real Example — Count Cars Sold by Branch AND Type:
| Branch | Product | Count |
|---|---|---|
| Alabama | Sedan | 4 |
| California | SUV | 2 |
| Florida | Hatchback | 6 |
| Washington | Convertibles | 1 |
Average values matching MULTIPLE conditions simultaneously
Syntax: =AVERAGEIFS(average_range, range1, criteria1, range2, criteria2, ...)
Real Example — Average Sale by Branch AND Product:
| Branch | Product | Average |
|---|---|---|
| Alabama | Sedan | $10,412.75 |
| California | SUV | $10,250.00 |
| Florida | Hatchback | $10,097.67 |
| Washington | Convertibles | $5,500.00 |
Multiply arrays then sum them — a powerful alternative to SUMIFS with more flexibility
Syntax: =SUMPRODUCT(array1, array2, ...)
Example #1 — With a Revenue Column:
=SUMPRODUCT((Branch="Alabama") * (Product="Quarter panel") * Revenue)
→ Result: $312.20
Example #2 — Without a Revenue Column (calculate on the fly):
=SUMPRODUCT((Branch="Alabama") * (Product="Quarter panel") * (Price * Quantity))
→ Result: $312.20 ✅ (Same result — no pre-calculated revenue column needed!)
Example #3 — SUMPRODUCT with Date Condition (advantage over SUMIFS):
=SUMPRODUCT((YEAR(Date)=2018) * (Branch="Alabama") * (Product="Quarter panel") * (Price * Quantity))
→ Filters by year — something SUMIFS cannot do as cleanly
Return the absolute value — remove the negative sign from any number
Syntax: =ABS(number)
Real Example — Trial Balance Presentation:
A company's trial balance showed liability accounts with negative signs. For the presentation, the General Manager wanted all values shown as positive.
| Account | Consolidated Balance | Absolute Value |
|---|---|---|
| ACCOUNTS PAYABLE-TRADE | -295,379,647 | 295,379,647 |
| ACCRUED EXPENSES | -16,432,621 | 16,432,621 |
| WITHHOLDING TAX PAYABLES | -134,471 | 134,471 |
=ABS(E5) → removes the negative sign from any cell
Round numbers to any decimal place, or to the nearest 10, 100, 1,000+
Syntax:
=ROUND(number, num_digits)
=ROUNDUP(number, num_digits)
=ROUNDDOWN(number, num_digits)
Positive digits = decimal places. Negative digits = round to tens/hundreds/thousands.
Quick Reference Cheat Sheet:
| Round Type | Formula | Raw Value | Result |
|---|---|---|---|
| Nearest dollar | =ROUND(A1, 0) |
1000.6 | 1,001 |
| 1 decimal place | =ROUND(A1, 1) |
100.66 | 100.7 |
| 2 decimal places | =ROUND(A1, 2) |
100.669 | 100.67 |
| Round up to dollar | =ROUNDUP(A1, 0) |
1000.6 | 1,001 |
| Round down to dollar | =ROUNDDOWN(A1, 0) |
1000.6 | 1,000 |
| Nearest 10 | =ROUND(A1, -1) |
223,436 | 223,440 |
| Nearest 100 | =ROUND(A1, -2) |
223,436 | 223,400 |
| Nearest 1,000 | =ROUND(A1, -3) |
223,436 | 223,000 |
| Nearest 10,000 | =ROUND(A1, -4) |
223,436 | 220,000 |
University Grading Example:
| Subject | Average Grade | ROUND | ROUNDUP | ROUNDDOWN |
|---|---|---|---|---|
| Math | 89.5 | 90 | 90 | 89 |
| Reading | 69.1 | 69 | 70 | 69 |
| Accounting | 90.9 | 91 | 91 | 90 |
Count the number of empty/blank cells in a range
Syntax: =COUNTBLANK(range)
Use case: Quality-checking data imports to find missing values before analysis.
=COUNTBLANK(A2:H2) → returns 2 if 2 cells in that row are empty
Generate a random decimal number between 0 and 1
Syntax: =RAND()
⚠️ The values change every time the sheet recalculates. To preserve the randomized values, copy the cells and paste as Values Only (Alt+E+S+V).
Generate a random whole number between a minimum and maximum value
Syntax: =RANDBETWEEN(bottom, top)
=RANDBETWEEN(20000, 50000) → returns a random whole number between 20,000 and 50,000
⚠️ Same as RAND — values change on every recalculation. Paste as values to preserve them.
Flip data from vertical to horizontal (or horizontal to vertical)
Syntax: =TRANSPOSE(array) then press Ctrl + Shift + Enter
Real Example — Salesforce Data Export:
Data came in vertically but was needed horizontally for a report:
Before (Vertical):
| Sales | Date | Month |
|---|---|---|
| 100 | Jan 2020 | January |
| 200 | Feb 2020 | February |
| 300 | Mar 2020 | March |
After TRANSPOSE (Horizontal):
| Sales | 100 | 200 | 300 |
|---|---|---|---|
| Month | January | February | March |
💡 Alternative method: Copy data → Paste Special → tick the Transpose checkbox. This is quicker but does NOT auto-update if the source data changes.
Look up a value in the leftmost column of a table and return a value from another column
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example #1 — Employee HR Lookup:
=VLOOKUP(C36, C42:O481, 2, FALSE) → returns Unit for Employee-12
Result: Unit-2
| Name | Unit | Role | Gross Pay |
|---|---|---|---|
| Employee-12 | Unit-2 | Other | 55,795 |
Example #2 — VLOOKUP with MATCH (column-shift proof):
=VLOOKUP($C$36, $C$42:$O$481, MATCH(D$35, $C$42:$O$42, 0), FALSE)
💡 Using MATCH inside VLOOKUP makes the formula column-shift proof — if someone inserts a new column, the formula still finds the right data automatically.
Look up a value in the top row of a table and return a value from a row below
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Use case: Looking up pricing from a horizontal pricing table.
Example — Johnathan's Fruit Market:
| Month | Apple | Banana | Grape |
|---|---|---|---|
| January | 12 | 10 | 15 |
| March | 16 | 12 | 18 |
Basic: =HLOOKUP(E42, J47:L49, 2, FALSE)
With MATCH: =HLOOKUP(E42, J47:L49, MATCH(D42, $I$47:$I$49, 0), FALSE)
💡 Using MATCH inside HLOOKUP protects the formula from breaking if rows are inserted.
Return the value of a cell at a specific row and column position within a table
Syntax: =INDEX(array, row_num, [col_num])
Real Example — Quarterly Sales by Branch and Vehicle Type:
The General Manager had 3 picklists (Quarter, Branch, Vehicle Type) and wanted to look up sales data dynamically:
=@INDEX((Q1_table, Q2_table, Q3_table, Q4_table),
VLOOKUP(Branch, ref_table, 2, FALSE),
VLOOKUP(Quarter, ref_table, 2, FALSE),
VLOOKUP(VehicleType, ref_table, 2, FALSE))
Drop Down Selection:
| Input | Selected |
|---|---|
| Branch Location | Washington |
| Fiscal Quarter | Q3 |
| Type of Vehicle | Sedan |
| Result | $55,123 |
Find the relative position of a value within a range
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
Real Example — Finding a Purchase Receipt:
An auditor questioned a specific item sold on Oct 31, 2019. The inventory clerk needed to find which purchase number it was to pull the physical receipt from the correct shift drawer.
=MATCH("00012429", Serial_Code_Column, 0)
→ Result: 8 (it was the 8th purchase of the day — morning shift)
| Serial Code | Product | Cost/Unit |
|---|---|---|
| 00012345 | Headlight motor | 20 |
| ... | ... | ... |
| 00012429 | Rear Right Side Door Glass | 44 ← position 8 |
Return a range that is offset by a specified number of rows and columns from a starting reference
Syntax: =OFFSET(reference, rows, cols, [height], [width])
Example #1 — Student Grade Lookup:
John's Math grade: =OFFSET(A1, 4, 3) → 65
John's total marks: =OFFSET(A1, 4, 1, 1, 5) → SUM of all 5 subjects = 398
Example #2 — Reverse Chronological P&L:
When P&L data runs left to right (oldest to newest) but you need it right to left (newest to oldest):
=OFFSET($C$46, COUNTA($C$71:$C71), COUNTA($C$45:$L$45) - D$45)
→ Automatically reverses the column order
Return the column number of a referenced cell
Syntax: =COLUMN([reference])
=COLUMN(E5) → returns 5 (column E is the 5th column)
=COLUMN(G5) → returns 7
=COLUMN(C5) → returns 3
Return the row number of a referenced cell
Syntax: =ROW([reference])
=ROW(A30) → returns 30
=ROW(A34) → returns 34
=ROW(A46) → returns 46
Count the number of columns or rows in a range
=COLUMNS(A1:E1) → returns 5 (5 columns in the range)
=ROWS(A1:A10) → returns 10 (10 rows in the range)
Return a value from a list based on an index number
Syntax: =CHOOSE(index_num, value1, value2, value3, ...)
Real Example — Student GPA Comment:
=CHOOSE(GPA, "Poor", "Average", "Good", "Outstanding")
| Name | GPA | Result |
|---|---|---|
| Matthew | 3 | Good |
| Rehan | 4 | Outstanding |
| Wyatt | 2 | Average |
| Kelly | 1 | Poor |
| Scott | 4 | Outstanding |
Values must be in sequential order: 1=Poor, 2=Average, 3=Good, 4=Outstanding
Find the smallest, largest, and middle value in a dataset
Syntax:
=MAX(range) ← Largest value
=MIN(range) ← Smallest value
=MEDIAN(range) ← Middle value
Real Example — Car Dealership Sales Analysis:
| Function | Result |
|---|---|
| MAX | $19,000 |
| MIN | $1,250 |
| MEDIAN | $11,215 |
Find the min or max value with one or more conditions applied
Syntax: =MINIFS(min_range, criteria_range1, criteria1, ...)
Example #1 — Min/Max by Branch:
| Branch | MINIFS | MAXIFS |
|---|---|---|
| Washington | 57 | 4,928 |
| Alabama | 28.80 | 1,664 |
| California | 7 | 1,100 |
| Florida | 21.20 | 460 |
Example #2 — Min/Max by Branch AND Product:
| Branch | Product | MINIFS | MAXIFS |
|---|---|---|---|
| California | Speedometer | 45 | 1,100 |
| Florida | Speedometer | 54 | 460 |
| Washington | Speedometer | 100 | 100 |
Rank numbers from highest to lowest or lowest to highest
Syntax: =RANK(number, ref, [order]) — 0 = Descending, 1 = Ascending
Real Example — Car Dealership Division Rankings:
| Division | Sales Amount | Rank (Desc) | Rank (Asc) |
|---|---|---|---|
| Division 9 | $1,500,000 | 1 | 12 |
| Division 6 | $1,300,000 | 2 | 11 |
| Division 2 | $1,200,000 | 3 | 10 |
| Division 4 | $876,000 | 12 | 1 |
Find the correlation coefficient between two data sets — how closely two things are related
Syntax: =CORREL(array1, array2) → returns a value between -1 and +1
Example #1 — Study Hours vs Test Scores:
=CORREL(study_hours, test_results) → 0.965 (97% correlation)
The more hours students studied, the higher their test scores. Very strong positive correlation.
Example #2 — Antique Demand Over Time:
=CORREL(years, demand_units) → 0.724 (72% correlation)
Antique demand grows as time passes — a moderately strong correlation.
Calculate the slope of a linear regression line through data points (rise over run)
Syntax: =SLOPE(known_ys, known_xs)
Real Example — FastCar Plan vs Actual:
=SLOPE(Actual_Sales, Plan_Sales) → 0.826
The slope of 0.826 shows the relationship between planned and actual car unit sales by month. A slope near 1.0 would indicate plan and actual are almost identical.
Predict future values based on historical trends
Syntax: =FORECAST(x, known_ys, known_xs)
Example #1 — Car Sales Forecast for 2021:
Historical data: Jan–Jun 2020 actual sales
=FORECAST(44197, historical_sales, historical_months) → 34.2 cars predicted
| Month (2021) | Forecasted Sales |
|---|---|
| January | 34.2 |
| February | 35.6 |
| March | 36.9 |
| April | 38.3 |
| May | 39.7 |
| June | 41.2 |
⚠️ FORECAST uses historical trends only — it cannot account for market changes, external events, or new variables.
Count how many values fall within defined ranges (bins)
Syntax: =FREQUENCY(data_array, bin_array) — enter with Ctrl+Shift+Enter
Real Example — Employee Salary Distribution:
| Salary Range | Count | Description |
|---|---|---|
| Up to $10,000 | 8 | At exactly $10,000 |
| $10,001 – $14,999 | 19 | Entry level |
| $15,000 – $19,999 | 16 | Mid level |
| $20,000 – $24,999 | 5 | Senior |
| $25,000 – $29,999 | 1 | Management |
Check if a cell contains a number — returns TRUE or FALSE
Syntax: =ISNUMBER(value)
Real Example — Annualised Sales Calculation:
Some cells in the Amount column contained text like "as45-", "fd", "vd" instead of numbers. The formula skips them:
=IF(ISNUMBER(G29) = TRUE, G29 * 365, 0)
| Name | Amount | ISNUMBER | Annualised |
|---|---|---|---|
| Betsy | 137.20 | TRUE | 50,079.66 |
| Ashley | as45- | FALSE | 0 |
| Hallagan | 152.01 | TRUE | 55,482.67 |
Find the minimum value in a database that matches specific criteria
Syntax: =DMIN(database, field, criteria)
Real Example — Fruit Sales Min Price:
| Fruit | Criteria | DMIN | Explanation |
|---|---|---|---|
| Apple | Qty > 3 | 4 | Lowest rate when more than 3 apples sold |
| Banana | Qty > 2 | 5 | Lowest rate when more than 2 bananas sold |
| Grapes | Qty > 5 | 10 | Lowest rate when more than 5 grapes sold |
Find the maximum value in a database that matches specific criteria
Syntax: =DMAX(database, field, criteria)
Real Example — Fruit Sales Max Price:
| Fruit | Criteria | DMAX | Explanation |
|---|---|---|---|
| Apple | Qty < 5 | 15 | Highest rate when less than 5 apples sold |
| Banana | Qty > 5 | 18 | Highest rate when more than 5 bananas sold |
| Grapes | Qty > 5 | 17 | Highest rate when more than 5 grapes sold |
Return different results based on a true/false test
Syntax: =IF(logical_test, value_if_true, value_if_false)
Real Example — Car Bid Accept/Reject:
A car dealership receives bids. If the bid price exceeds the floor price → Accept. If not → Reject.
=IF(D2 > C2, "Accept Offer", "Reject Bid")
| Car Model | Floor Price | Bid Price | Decision |
|---|---|---|---|
| Honda Civic | $15,000 | $10,000 | Reject Bid |
| Audi A3 | $30,000 | $31,000 | Accept Offer |
| Ford Fiesta | $9,000 | $11,000 | Accept Offer |
| Toyota Corolla | $25,000 | $17,000 | Reject Bid |
Return a result only when ALL conditions are met at the same time
Syntax: =IF(AND(condition1, condition2, ...), value_if_true, value_if_false)
Real Example — Salesperson Bonus (BOTH criteria must be met):
Criteria: Units Sold > 6 AND Total Sales > $10,000
=IF(AND(C2>6, B2>10000), B2*5%, 0)
| Salesperson | Total Sales | Units Sold | Bonus |
|---|---|---|---|
| Erika Hexacta | $12,000 | 8 | $600 ✅ (both met) |
| Evan Alex | $6,500 | 7 | $0 ❌ (sales too low) |
| Adam Benko | $16,000 | 2 | $0 ❌ (units too low) |
| Josephine London | $21,000 | 7 | $1,050 ✅ (both met) |
Return a result when ANY ONE condition is met
Syntax: =IF(OR(condition1, condition2, ...), value_if_true, value_if_false)
Real Example — Salesperson Bonus (EITHER criteria qualifies):
=IF(OR(C2>6, B2>10000), B2*5%, 0)
| Salesperson | Total Sales | Units Sold | Bonus |
|---|---|---|---|
| Erika Salazar | $12,000 | 8 | $600 ✅ (both met — qualifies) |
| Evan Alex | $6,500 | 7 | $325 ✅ (units met — qualifies) |
| William Jacobs | $9,900 | 5 | $0 ❌ (neither met) |
| Josephine London | $9,500 | 4 | $0 ❌ (neither met) |
Net Present Value — calculate whether a future investment is worth pursuing today
Syntax: =NPV(rate, value1, value2, ...) + initial_investment
Formula logic:
NPV = -(Initial Investment) + Sum of (Future Cash Flows discounted at the rate)
Real Example — $120,000 Investment, 1% monthly discount rate:
| Month | Future Cash Flow | Discounted Value |
|---|---|---|
| 1 | $25,000 | $24,752 |
| 2 | $25,000 | $24,507 |
| ... | ... | ... |
| 12 | $25,000 | $22,186 |
| Total Discounted | $281,377 | |
| NPV | $161,377 |
=NPV(0.01, D27:D38) + D41 → $161,377
A positive NPV means the investment creates value — it should be pursued.
Internal Rate of Return — find the discount rate that makes NPV equal to zero
Syntax: =XIRR(values, dates) ← more accurate than IRR
Example:
Investment: -$100 today
Return: $120 in year 1
Company cost of capital: 8%
Manual calculation:
0 = -100 + 120/(1+R%)
R% = 20% → IRR is 20%
Since IRR (20%) > Cost of Capital (8%) → ✅ Accept the project.
💡 Rule: If IRR > Cost of Capital → accept. If IRR < Cost of Capital → reject.
Calculate the fixed payment (monthly or annual) for a loan
Syntax: =PMT(rate, nper, pv, [fv], [type])
Real Example — $1,000,000 Equipment Loan:
| Option A | Option B | Option C | |
|---|---|---|---|
| Interest Rate | 5% | 5% | 10% |
| Loan Term | 6 years | 6 years | 20 years |
| Payment Timing | Beginning | End | Beginning |
| Monthly Payment | $16,038 | $16,105 | $9,570 |
| Total Paid | $1,154,744 | $1,159,555 | $2,296,911 |
Calculate the PRINCIPAL portion of a specific loan payment period
Syntax: =PPMT(rate, per, nper, pv, [fv], [type])
Real Example — Month 12 Principal Payment:
| Option A (5%, 6yr) | Option B (8%, 12yr) | Option C (10%, 20yr) | |
|---|---|---|---|
| Monthly Principal | $12,445 | $4,473 | $1,443 |
Calculate the INTEREST portion of a specific loan payment period
Syntax: =IPMT(rate, per, nper, pv, [fv], [type])
Real Example — Month 12 Interest Payment:
| Option A (5%) | Option B (8%) | Option C (10%) | |
|---|---|---|---|
| Monthly Interest | $3,593 | $6,351 | $8,207 |
💡 PMT = PPMT + IPMT. Together they show the full breakdown of each loan payment.
Calculate the effective annual interest rate (accounts for compounding)
Syntax: =EFFECT(nominal_rate, npery)
Manual formula: Effective Rate = (1 + I/n)^n - 1
| Scenario | Nominal Rate | Periods/Year | Effective Rate |
|---|---|---|---|
| 1 | 8% | 2 | 8.16% |
| 2 | 10% | 12 | 10.47% |
| 3 | 12% | 12 | 12.68% |
The effective rate is always higher than the nominal rate due to compounding.
Calculate asset depreciation using the fixed-declining balance method
Syntax: =DB(cost, salvage, life, period, [month])
Real Example — 3 Equipment Purchases:
| Scenario 1 | Scenario 2 | Scenario 3 | |
|---|---|---|---|
| Cost | $2,500,000 | $1,100,000 | $500,000 |
| Salvage | $50,000 | $220,000 | $10,000 |
| Useful Life | 8 years | 5 years | 3 years |
| Year 1 Depreciation | $967,500 | $302,500 | $364,500 |
| Year 2 Depreciation | $593,078 | $219,313 | $98,780 |
Calculate the interest rate per period of a loan or investment
Syntax: =RATE(nper, pmt, pv, [fv], [type])
Real Example — 3 Loan Options:
| Scenario 1 | Scenario 2 | Scenario 3 | |
|---|---|---|---|
| Loan Amount | $3,500,000 | $1,000,000 | $500,000 |
| Monthly Payment | -$100,000 | -$80,000 | -$50,000 |
| Loan Years | 20 | 10 | 1 |
| Monthly Rate | 2.85% | 8.00% | 2.92% |
| Annual Rate | 34.25% | 96.0% | 35.07% |
Calculate the present value of future cash flows
Syntax: =PV(rate, nper, pmt, [fv], [type])
Real Example — 3 Investment Projects:
| Project 1 | Project 2 | Project 3 | |
|---|---|---|---|
| Monthly Payment | -$10,000 | -$8,000 | -$20,000 |
| Years | 10 | 10 | 4 |
| Discount Rate | 6% | 6% | 6% |
| Present Value | $905,238 | $724,191 | $855,864 |
Calculate the future value of a loan or investment
Syntax: =FV(rate, nper, pmt, [pv], [type])
Real Example — Loan Interest Earned:
| Scenario 1 | Scenario 2 | Scenario 3 | |
|---|---|---|---|
| Loan Amount | $5,000 | $5,000 | $6,000 |
| Interest Rate | 10% | 12% | 11% |
| Years | 3 | 4 | 4 |
| Future Value | $6,655 | $7,868 | $9,108 |
| Total Interest Earned | $1,655 | $2,868 | $3,108 |
| Part | Category | Topics | Link |
|---|---|---|---|
| Part 1 | Navigation & Formatting | 14 Topics | 🔗 Open Part 1 |
| Part 2 | Functions | 7 Topics | 🔗 Open Part 2 |
| Part 3 | Formulas | 57 Formulas | ✅ You are here |
MIT License — Free to use, share, and learn from.
