Saturday, September 23, 2023

### 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...

### 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,...

### 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,...

### 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...

### 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...

### 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...

### 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...

### 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...

### 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...

### 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...

### 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...

### 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...