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

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
    SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell for many items.

    How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel

    Formulas & Functions Charley Kyd -
    0
    SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell...
    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...
    INDEX-MATCH is Excel's most-powerful lookup method. But many users find it challenging. This is the first of a three-part series designed to INDEX-MATCH clearer.

    How to Use INDEX-MATCH, Part 1: The INDEX Function

    Formulas & Functions Charley Kyd -
    0
    Which is best, VLOOKUP or INDEX-MATCH? You might be surprised at the question, because the answer never has been in doubt. INDEX-MATCH can do anything...
    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...
    Simple spreadsheet edits can introduce costly Excel errors. Here are three techniques that can help you to reduce your errors in Excel.

    Three Ways to Reduce Errors in Your Excel SUM Formulas

    Error Reduction Charley Kyd -
    0
    If you aren't careful, simple edits to your reports and analyses can cause significant errors. Years ago, for example, I heard about an expensive error...
    We compare Excel's five columnar lookup functions.

    XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

    Formulas & Functions Charley Kyd -
    0
    SUMIFS? Really? Why is SUMIFS included among those lookup methods? I'll explain in a few minutes. But first, let's look at the lookup methods shown in...
    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...
    You need a volatile workbook to test Excel calculation speeds. Here's how I set one up to test lookup formulas.

    A Volatile Workbook to Test Calculation Times for Excel Lookup Methods

    Formulas & Functions Charley Kyd -
    0
    In Use Excel VBA to Test Report Calculation Times I described a macro that finds the total time necessary to calculate Excel for a specified number...
    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.

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

    Formulas & Functions Charley Kyd -
    0
    At first glance, the following report is an ordinary one. It merely sums an Excel Table by Product and Region, and then foots and...
    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?

    Formulas & Functions Charley Kyd -
    0
    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...
    1234...8Page 3 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.