Friday, January 22, 2021
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...
    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,...
    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...
    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...
    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...
    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 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...
    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...
    123...7Page 1 of 7

    Latest Articles

    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...
    How to aggregate named groups of GL accounts.

    How to Report GL Account Groups in Excel

    Accounting & Finance Charley Kyd -
    0
    Believe it or not, this income statement is quite sophisticated. It's not nearly as simple-minded as it looks. In fact, this income statement illustrates a...
    If you want all your Excel reports, analyses, forecasts, and other Excel work to be highly productive, this is the only strategy that will work for you.

    An Introduction to Excel Data Plumbing

    Business & Economics Charley Kyd -
    0
    Would you like to: Create your new reports, analyses, forecasts, and other Excel work quickly? Update your Excel work with one command, without using...
    Advertisement
    Advertisement
    © ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.