Monday, March 1, 2021
Home Formulas & Functions Page 2

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
    Excel's dynamic range names give your formulas the power to adapt automatically in response to changes in your data or settings. Here's how to set them up.

    How to Create and Use Dynamic Range Names in Excel

    Formulas & Functions Charley Kyd -
    0
    (Download the example workbooks.) We Excel users often refer to ranges that need to move or expand in future versions of our reports. For example: ...
    To calculate the correct growth rate for your data you need to be clear about what you want your growth rate to signify. Here's how to work it out.

    How to Calculate BOTH Types of Compound Growth Rates in Excel

    Formulas & Functions Charley Kyd -
    0
    If you search the web to learn how to calculate a compound growth rate in Excel, you’ll likely find instructions for calculating only one...
    Although Excel's FREQUENCY function was designed to calculate frequency distributions, you also can use the SUM-IF, SUMPRODUCT, INDEX-FREQUENCY, and COUNTIFS functions. Here's a summary of the methods and your options.

    Five Ways to Calculate Frequency Distributions in Excel

    Formulas & Functions Charley Kyd -
    0
    Walt captures blood-pressure readings and wants to find how often the readings fall into various ranges of values. This is a common need for a...
    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 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...
    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...
    Most Excel users need to look up data in workbooks. But what's the best lookup method?

    Excel’s VLOOKUP vs INDEX-MATCH Functions

    Formulas & Functions Charley Kyd -
    0
    Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH. Although the two methods are similar, INDEX-MATCH is more powerful. I suspect, however, that VLOOKUP is better-known and more...
    Pivot Tables aren't merely a way to interact with your data. You also can use them as a rich source of data for standard reports and analyses. And you don't need to limit formulas to GETPIVOTDATA; you also can use SUMIFS, SUMPRODUCT, and all other Excel functions with pivots. Here's how.

    How to Set Up a Pivot Table as an Excel-Friendly Database

    Formulas & Functions Charley Kyd -
    0
    You can use a Pivot Table as a database in the same way that you can use Excel Tables and other Excel-Friendly Databases (EFDs). In...
    The Internal Rate of Return calculation has very real problems, no matter where it's calculated. But with its MIRR function, Excel could offer a solution.

    How Excel’s MIRR Function Can Fix the IRR Function

    Formulas & Functions Charley Kyd -
    0
    The calculation of an Internal Rate of Return is very appealing...at first glance. When you know the IRR of a prospective investment, you seem to...
    In a list of items that could appear in a worksheet any number of times, here's how to count the number of items that appear only once. Or twice. And so on.

    How to Count the Occurrences of Items in a List

    Formulas & Functions Charley Kyd -
    0
    "I have a list of repair orders (RO). I want to know how many one line repair orders there are in the list. So...
    123...8Page 2 of 8

    Latest Articles

    The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

    How to Use Excel’s LET Function

    Formulas & Functions Charley Kyd -
    0
    In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
    You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

    How to Set Up an Automatic Error-Checking System in Excel Reports

    Accounting & Finance Charley Kyd -
    0
    Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
    Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.

    How to Stack and Shelve Dynamic Arrays

    Formulas & Functions Charley Kyd -
    0
    While using Excel 365 recently, I needed to create one dynamic array that would consist of two arrays, with one stacked on top of...
    Advertisement
    Advertisement
    © ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.