Monday, December 5, 2022
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.

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...
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 Smooth Monthly Trends with Centered Moving Averages

I recently read an article in the Wall Street Journal about the expected increase of electric vehicles in the US. That made me wonder...
To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.

How to Foot and Cross-Foot Excel Reports in a Floating-Point World

At first glance, the following report is an ordinary one. It merely sums an Excel Table by Product and Region, and then foots and...

Excel’s XLOOKUP Function Explained

Excel's XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't...

Excel’s VLOOKUP Function Explained

Excel's VLOOKUP function looks in the first column of an array and moves across the row to return the value of a cell. VLOOKUP is...
Two unusual formulas can use the SEARCH or FIND functions to search any number of cells for any number of words—almost instantly.

How I Set Up SEARCH and FIND Formulas on Steroids

I've used Excel long enough to recognize when I've stumbled across an unusual formula. And it happened again a few minutes ago. In the past,...
Here's how to calculate the annual rate of inflation both by comparing prices several years apart, and by several months apart.

How to Calculate Inflation in Three Different Ways

Today, in the third quarter of 2021, the business press is churning out an avalanche of articles about the return of inflation. Although some...
It should have been a simple Excel formula. But it became a two-part puzzle needing arrays, ASCII, and text concatenation to answer.

A Two-Part Excel Puzzle, with Arrays, Text, ASCII, and Concatenation

Recently, I needed to compare two long columns of text of various lengths to make sure that each row of text in column A...
Excel users made such a mess of nested IF functions that Microsoft created the IFS function to help. Here's how to tame both functions.

How to Tame Nested IF Functions in Excel

When I review my Excel formulas from many years ago, one of the types of formulas that I'm most embarrassed about today is the...

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