Thursday, December 7, 2023
Home Formulas & Functions

Formulas & Functions

Virtually everything business users do with Excel involves worksheet formulas and functions. And this category concentrates on that topic.

This category also includes what Microsoft calls “Names”—which many of us call “Range Names.” More accurately, however, “Names” are named formulas.

Check tags for information about specific functions.

As inflation rises, so do mortgage interest rates—causing house prices to fall. Here's how Excel's PV function can help you estimate what you new house price will be.

Here’s How Inflation Could Affect the Value of Your Home

November 5, 2021 Inflation is rising in the U.S. And that made me wonder how a higher rate of inflation could affect the price of...
How to Work with Dates Before 1900 in Excel

How to Work with Dates Before 1900 in Excel

(Download the workbook.) If you work with dates prior to the year 1900, Excel's standard date-handling system will be no help. However, there are several...
Does Excel's INDEX worksheet function work for you only some of the time? Here are ways to learn why your INDEX function isn't working as you expect.

How to Debug INDEX in Excel

"I'm not a very experienced Excel user. Why does = INDEX(...) sometimes work and sometimes not.... :-)?" -- Suzan G. Probably the best way to...
Microsoft tells us that many worksheet functions are 'deprecated' or that thy're 'compatibility' functions. Here what that means and why you should care.

What’s a ‘Deprecated’ Function in Excel?

Wikipedia tells us that deprecation is a status applied to a computer software feature, characteristic, or practice indicating it should be avoided, typically because of being superseded. Each new generation of...
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...
Excel's STOCKHISTORY function can return decades of history about the prices of stocks for thousands of public companies from many countries. Here's an introduction to that function.

Introducing Excel’s STOCKHISTORY Function

The STOCKHISTORY function recently showed up in my non-beta version of Excel 365. Its appearance was a surprise because it wasn't announced as a What's...
Excel's CLEAN function does more than Excel's help topic says. In fact, it cleans all but two nonprinting characters in the ASCII and Unicode character sets. Here's how to deal with nonprinting characters.

Excel’s CLEAN Function is More Powerful Than You Think

The Excel 2016 help file for the CLEAN function provides more information than earlier versions: “Removes all nonprintable characters from text. Use CLEAN on text imported...
These tables summarize the performance of Excel's 15 worksheet functions use different methods to round data.

Overview of Excel’s Rounding Functions

Since Version 2013, Excel has offered 15 functions that round data in different ways. The first table describes each function briefly. The second table summarizes...
Here are nineteen examples of common date calculations used in Excel reports and analyses, using worksheet functions like DATE, EDATE, EOMONTH, and others.

Nineteen Date Calculations in Excel

Excel reports and analyses typically include date calculations. But many Excel users have large gaps in their knowledge about this topic. So in this article,...
In one SUMPRODUCT formula, you can summarize any number of specific accounts in a list. This makes it easy to summarize financial data from an accounting trial balance.

How to Create Summarized Financial Statements with SUMIFS Criteria Lists

In How to Use SUMIFS with Criteria Lists, Summarizing Sales, I explained how to use the SUMPRODUCT function with SUMIFS to return the sum of...

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