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.

    Random
    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

    Formulas & Functions Charley Kyd -
    0
    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...
    How to Use SUMPRODUCT to Find the Last Item in an Excel List

    How to Use SUMPRODUCT to Find the Last Item in an Excel List

    Formulas & Functions Charley Kyd -
    0
    Suppose you have a Sequential List of items, as in the second table in the figure below. And suppose you want to show the...
    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...
    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...
    You can use probability distributions to manage the uncertainty about your assumptions when you create simulations in Excel. Here's how.

    How to Return Random Numbers from a Normal Distribution for Your Excel Forecasts

    Formulas & Functions Charley Kyd -
    0
    (This is the first of two articles about normal distributions. The second article is, How to Create Monte Carlo Models and Forecasts Using Excel Data...
    This simple VBA procedure finds the precise time it takes to calculate a workbook. You can use it to test competing workbook design methods.

    Use Excel VBA to Test Report Calculation Times

    Lookup Formulas and Functions Charley Kyd -
    0
    I once exchanged email with an experienced Excel jockey who insisted that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said,...
    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...
    How to calculate and highlight the standard error of the estimate on each side of the trend in an Excel chart to make exceptional results stand out.

    Highlight Normal Results in Line Charts to Make Exceptional Results Stand Out

    Charting Methods Charley Kyd -
    0
    Line charts that show trends in performance are the most useful type of chart that management reports can contain. All managers want good performance to...
    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...
    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...
    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.