SUMX2MY2 Function in Excel: Sum of Differences of Squares
Maths & trigLevel: ExpertAvailable in: Excel 2007+SUMX2MY2 returns Σ(x² - y²) for two matching ranges.Syntax=SUMX2MY2(array_x, array_y)ArgumentWhat it.
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
Calculate Age and Tenure in Excel: Years, Months and Days (DATEDIF and YEARFRAC)
Calculate Age and Tenure in Excel: Years, Months and Days (DATEDIF and YEARFRAC)
Exact age or service tenure as "5 years 3 months 12 days", completed years for gratuity, age on a specific date, decimal years with…
Read article →Excel for Finance Lesson 8: Financial Ratios From the P&L and Balance Sheet
Excel for Finance Lesson 8: Financial Ratios From the P&L and Balance Sheet
Calculate and read the key ratios in Excel: gross and net margin, current and quick ratio, debt-equity, interest coverage, debtor and inventory days, ROE…
Read article →Excel for Finance Lesson 7: Fixed Asset Register and Depreciation
Excel for Finance Lesson 7: Fixed Asset Register and Depreciation
Keep a fixed asset register in Excel: SLM depreciation with SLN, WDV at block rates, months of use in the first year, the half-rate…
Read article →Excel for Finance Lesson 6: Budget vs Actual With Variances
Excel for Finance Lesson 6: Budget vs Actual With Variances
Build a budget vs actual report: variance and variance %, favourable/adverse logic that differs for income and costs, conditional formatting, largest variances, and a…
Read article →Excel for Finance Lesson 5: 13-Week Cash Flow Forecast
Excel for Finance Lesson 5: 13-Week Cash Flow Forecast
Build a rolling 13-week cash flow forecast in Excel: weekly collections and payments, running closing balance, minimum-cash alerts, the lowest point, and how to…
Read article →RECEIVED Function in Excel: Amount Received at Maturity
RECEIVED Function in Excel: Amount Received at Maturity
FinancialLevel: ExpertAvailable in: Excel 2007+RECEIVED calculates what you get back at maturity for a discounted security.Syntax=RECEIVED(settlement.
Read article →PDURATION Function in Excel: Years Needed to Reach a Target
PDURATION Function in Excel: Years Needed to Reach a Target
PDURATION returns how many periods an investment needs to grow to a target at a given rate (lump sum, no regular payments).
Read article →HARMEAN Function in Excel: The Right Average for Rates
HARMEAN Function in Excel: The Right Average for Rates
HARMEAN averages rates properly. Drive 60 km/h one way and 40 km/h back, and your average speed is 48, not 50.
Read article →SEC Function in Excel: Secant
SEC Function in Excel: Secant
Maths & trigLevel: ExpertAvailable in: Excel 2013+SEC returns 1/COS(x).Syntax=SEC(number)ArgumentWhat it meansnumberValue (angles in.
Read article →GEOMEAN Function in Excel: Average Growth Rate
GEOMEAN Function in Excel: Average Growth Rate
GEOMEAN multiplies values and takes the n-th root. For growth rates it gives the honest average: +50% followed by −50% is not an average…
Read article →Remove Duplicates in Excel: 3 Quick Ways (and Which to Use)
Remove Duplicates in Excel: 3 Quick Ways (and Which to Use)
Remove or find duplicates in Excel three ways: the Remove Duplicates button, Advanced Filter unique records, and UNIQUE/COUNTIF formulas. Which to use when, how…
Read article →