Saturday, September 24, 2022
Excel provides several worksheet functions for working with normal distributions or 'bell-shaped curves.' This introduction to Excel's Normal Distribution functions offers help for the statistically challenged.

An Introduction to Excel’s Normal Distribution Functions

(Download the workbook.) When a visitor asked me how to generate a random number from a Normal distribution she set me to thinking about doing statistics...
Term loans can have a variety of repayment periods, interest rates, amortizing methods, and so on. Here's how to calculate amortization schedules for the two most common types of amortizing loans.

How to Use Excel Formulas to Calculate a Term-Loan Amortization Schedule

"How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task,...
Excel offers at least three ways to set up data so your reports and analyses can use it easily as a reliable data source.

Introducing Excel’s Three Types of Spreadsheet Databases

Excel offers three general ways to arrange data in your spreadsheet so you can use it as a database with your worksheet formulas: Simple...
The Monte Carlo method allows you to forecast performance using probability distributions for your assumptions. And Data Tables provide a quick and easy way to implement the Monte Carlo method. Here's how to do it.

How to Create Monte Carlo Models and Forecasts Using Excel Data Tables

(Download the workbook.) (This is the second of two articles about normal distributions. The first article is, How to Return Random Numbers from a Normal...
Here's how to use a formula that returns TRUE or FALSE in Excel's conditional formatting feature to highlight rows that contain specific numbers or text.

How to Use Conditional-Format Formulas to Change Background Colors

"I have an Excel table with a column that will be filled with 'yes' or 'no. When a user enters 'yes' to a cell,...
The SUMIFS function returns a sum for nearly any number of single criteria. But with SUMPFODUCT you can return the sum for many criteria listed in a Criteria List.

How to Use SUMIFS with Criteria Lists, Summarizing Sales

With one exception, SUMIFS is a very powerful function. And it's very fast. To understand the one exception, suppose you have a table of sales...
Most loans and many investments are annuities, which are payments made at fixed intervals over time. Here's how to use Excel to calculate any of the five key unknowns for any annuity.

Excel’s Five Annuity Functions

“Help!” the message said. “I know the payment, interest rate, and current balance of a loan, and I need to calculate the number of...
Excel offers several ways to summarize data quickly and easily. Here are the most powerful and flexible approaches, which include using Excel array formulas.

The Most Powerful Ways to Summarize Excel Data for Reporting and Analysis

(Note: I wrote this before Microsoft introduced Excel Tables or SUMIFS. This post is scheduled for an update.) Excel users often need to summarize data...
The extreme variability caused by seasonal sales makes it difficult to track and forecast your underlying sales trends. Here's how to solve that problem.

How to Create a Rolling Forecast of Seasonal Sales in Excel

The Excel chart below shows the typical saw-tooth pattern of seasonal sales. Seasonal sales have about the same pattern every year, every week, or both. In...
Which is faster: VLOOKUP? Or INDEX-MATCH? Here are the test results.

Excel’s Fastest Lookup Methods: The Tested Results

This article presents the results of my tests to find Excel’s fastest lookup method. I discussed the report workbook in A Volatile Workbook to Test Calculation...

Latest Articles

Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.

How Fast Is Too Fast?

(Originally published in Inc Magazine.) What typically tops the list of worries of the chief executive officers of fast growing companies? Financing that growth, according...

How to Smooth Data by Using the TREND Function

Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...
Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.

Consumer Sentiment Suggests a 2022 Recession

In recent months, business websites have speculated about recessions and stagflation in 2022. These predictions could affect your Excel work significantly in the next few...
Advertisement