Sunday, September 21, 2025
Linkedin
Linkedin
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.

    Most popular
    When you want to look up data in Excel, you should never use a worksheet function with "lookup" in its name. Here's why...

    Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel

    Formulas & Functions Charley Kyd - 0
    (Download the workbook.) Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a "lookup" function...
    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

    Business Statistics Charley Kyd - 0
    (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...
    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

    Formulas & Functions Charley Kyd - 0
    “Help!” the message said. “I know the payment, interest rate, and current balance of a loan, and I need to calculate the number of...
    Although Excel provides two worksheet functions that ignore filtered rows in a Table, nearly any function can ignore those hidden rows if you use this new trick.

    Use a ‘Visible’ Column in Formulas to Ignore Hidden Rows in Filtered Tables

    Formulas & Functions Charley Kyd - 0
    Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table. And slicers for Tables, introduced...
    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

    Formulas & Functions Charley Kyd - 0
    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 Work with Dates Before 1900 in Excel

    How to Work with Dates Before 1900 in Excel

    Dates and Times Charley Kyd - 0
    (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...
    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

    Formulas & Functions Charley Kyd - 0
    "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 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

    Formulas & Functions Charley Kyd - 0
    (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

    Formulas & Functions Charley Kyd - 0
    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 FREQUENCY function was first created to calculate frequency distribution tables, which are needed for charting histograms. But the COUNTIFS function offers more power, and it's easier to use.

    Use COUNTIFS, not FREQUENCY, to Calculate Frequency Distribution Tables for Charting Histograms

    Formulas & Functions Charley Kyd - 0
    Because the Texas and California governors have been bickering over the Texan's attempt to poach California employers, I got curious about the distribution of...
    123...9Page 1 of 9

    Latest Articles

    Excel Flowbook Revolution

    Getting Radical with Excel

    Excel Data Plumbing Revolution Charley Kyd - 0
    It's time to think about Excel in a radical new way—when we use it to work with business or economic data. It's time, in fact,...
    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?

    Accounting & Finance Charley Kyd - 0
    (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

    Formulas & Functions Charley Kyd - 0
    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...
    Advertisement
    © ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.