Saturday, February 27, 2021
Home Formulas & Functions Page 7

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
    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,...
    INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster. Here are six examples.

    How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can’t Do

    Formulas & Functions Charley Kyd -
    0
    In this three-part series, I'm showing you how to use INDEX-MATCH. In Part 1, I showed you how to use the INDEX function. In Part...

    How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas

    Formulas & Functions Charley Kyd -
    0
    A friend recently asked, "In this table, how can I return the date where the lowest value occurs?" More generally, however, he was asking how 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...
    Excel's SUMPRODUCT function offers much of the power of Excel array formulas, but without special treatment.

    Use Excel’s SUMPRODUCT to Summarize Worksheet Data

    Formulas & Functions Charley Kyd -
    0
    After I posted  The Most Powerful Ways to Summarize Excel Data for Reporting and Analysis, a reader asked why I hadn't discussed the SUMPRODUCT function. I...
    This simple invoicing system allows you to keep a list of products and prices in Excel, then use VLOOKUP or INDEX-MATCH to populate an invoice with the item and quantity you choose.

    Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs

    Accounting & Finance Charley Kyd -
    0
    A visitor asked how to set up a simple invoicing system in Excel. This is a common problem in many small businesses, divisions, and sales...
    Excel's range names offer great power and flexibility. And they're not hard to use. Here's an introduction to the power that Excel provides.

    Introducing the Power of Excel Range Names

    Range Names Charley Kyd -
    0
    Many Excel users seldom use range names. This fact always surprises me because Excel names are powerful, flexible, and easy to use. Excel offers two...
    Excel's four better-known functions for rounding numbers are: INT, ROUND, ROUNDUP, and ROUNDDOWN. Here's how to understand and use them in your Excel work.

    How to Use Excel’s Better-Known Rounding Functions: INT, ROUNDUP, ROUNDDOWN, & ROUND

    Formulas & Functions Charley Kyd -
    0
    Excel has one way to round up or down from the first generation. In recent generations it has also offered a second method. And...
    In Excel Tables, you can filter on any two conditions in a column. But by using the SUMPRODUCT function, you can filter on any number of items in a list.

    How to Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

    Data Management Charley Kyd -
    0
    Excel 2007 introduced the powerful Table feature, as illustrated below. Tables allow you to sort and filter your data easily. However, the filter capability has...
    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...
    1...678Page 7 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.