Posts

This is an array formula and must be entered with control + shift + enter. This formula uses the named ranges “name” (B5:B11) and “group” (C5:C11). The logical test checks each cell in the named range “group” for the color value in E5 (red in this case). The result is an array like this: That result is used in turn to filter names from the named range “name”: For each TRUE, the name survives, for each FALSE, IF returns an empty string ("")....

Just like any other error in Excel, the #NAME error also occurs in specific situations (which I will cover in this tutorial), and there are some simple ways to find and fix the #NAME error. Let’s get to it right away! What causes the #NAME Error? When you use a formula and it gives you a NAME error, it means that the formula can not recognize something in it. Let’s have a look at some of the most common issues that can cause a name error to raise its ugly head in your spreadsheet....

Let’s take a look. Here we have the same worksheet we looked at in an earlier lesson which tracks the hours worked and gross pay of a small team. Everyone is paid the same hourly wage, so we used an absolute reference to the hourly wage in our formulas. Let’s create a named range for the hourly rate and convert our formulas to use the named range instead of a cell address....

In the example shown, the formula in C6 is: Note: this formula assumes Excel’s default 1900 date system. In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturday’s in the future are evenly divisible by 14. The formula uses this fact to figure out 2nd Saturdays, then subtracts 1 to get the Friday previous....

Where age (E5:E16) and amount (D5:D16) are named ranges. See below for the formulas in I6 and I7, and the formulas in H5:H7. SUMIFS function The SUMIFS function is designed to sum cells that meet multiple criteria. SUMIFS takes at least three arguments like this: Notice sum_range appears first. Additional criteria are added in range/criteria pairs like this: The formulas used to sum invoices by age in I5:I7 are as follows: Notice that criteria appear in double quotes ("")....

where data is the named range B5:B16, and each text string includes both a label and a number separated by a single space (" “). See below for more details and for the formulas used to generate the summary table in E7:F9. Total sum To sum all the numbers that appear in B5:B16, ignoring text, the formula in E5 is: Working from the inside out, the TEXTAFTER function is used to extract the numbers like this: TEXTAFTER is configured to extract the text that occurs after a single space character (” “)....

You know something in Excel if you know how to use the VLOOKUP function. If you don’t, you better not list Excel as one of your strong areas in your resume. I have been a part of the panel interviews where as soon as the candidate mentioned Excel as his area of expertise, the first thing asked was – you got it – the VLOOKUP function. Now that we know how important this Excel function is, it makes sense to ace it completely to be able to proudly say – “I know a thing or two in Excel”....

To Right Click anywhere in the QAT to quickly access the Customize Quick Access Toolbar menu. In the resulting window there is a Choose commands from drop down menu, you will be able to find all commands listed here by selecting All commands. Excel Options Look for the command called Options from the All commands menu. This command will allow you to quickly open the Excel Options window instead to going to File menu > Options....

Large numbers are often used in an Excel spreadsheet, and this can make them difficult to read if they are not formatted properly. A number in millions or even billions can be difficult to assess and could easily be misread by a factor of 10. Fortunately, Excel provides several easy ways to format numbers with comma separators to help the user with readability. If you have ever viewed financial spreadsheets, the numbers are frequently rounded to the nearest thousand because the numbers are often so large....

Pivot tables are the best way to quickly summarize and analyze large sets of data in Excel. They are easy to use and you can have a summarized report for your dataset in a few quick clicks. There are a lot of entry points for pivot tables, and they are all worth knowing. Get your copy of the example workbook to follow along. Create an Excel Table for Your Dataset The first thing you need to know about pivot tables, is they work best when the source data is inside an Excel Table....

One of the issues in dealing with contact data is the format of their full name. For contacts whose full name appears as First Last, you would likely rather format it as Last, First. Doing so usually leads to easier sorting and filtering in your list. Also, last names are generally more unique than first names which makes any lookups more reliable. This post explores several different ways to reverse the first and last names in Excel....

For this example, we want to find the original amount of a loan with a 4.5% interest rate, and a payment of $93.22, and a term of 60 months. The PV function is configured as follows: rate - The interest rate per period. We divide the value in C5 by 12 since 4.5% represents annual interest: nper - the number of periods comes from cell C7, 60 monthly periods in a 5 year loan....

The result is a date 60 years (720 months) from the date of birth in column C. Note: At the time of this writing, the current date is September 2, 2021. This is the date used to calculate the remaining years in column F. The date comes from column C. For months, we need the equivalent of 60 years in months. Since most people don’t know how many months are in 60 years, a nice way to do this is to embed the calculation in the formula like this: Inside the EDATE function, Excel will perform the math and return 720 directly to EDATE as the months argument....

where birthdays is the named range (B5:B104), which contains 100 random birthdays. As the formula is copied down, it returns the total count of birthdays in each month as listed. Inside SUMPRODUCT, we have this expression: On the right, the MONTH function extracts the month for each date in the named range “birthdays”. On the right, the MONTH function is used to get a number for each month name shown in the table....

where data is the named range B5:B16. COUNTIF returns 3, since there are three cells that end with “R”. Note that COUNTIF is not case-sensitive. COUNTIF function The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument: question mark (?), asterisk(), or tilde (~). A question mark (?) matches any one character and an asterisk () matches zero or more characters of any kind....

The result is 9 since there are 6 orders that are complete and 3 orders that are pending. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range: Count cells in range1 that meet criteria1....

Download Example File Watch Video – Goal Seek in Excel One Variable Data Table in Excel. Two Variable Data Table in Excel. Scenario Manager in Excel. Excel Solver. Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value. For example, I have the data (as shown below), where I calculate the Monthly payment outflow with the given Loan Amount, Interest Rate and Number of Payments....

In this example, SUMPRODUCT is configured with two arrays. The first array is the range that holds product pricing: Note the reference is absolute to prevent changes as the formula is copied to the right. This range evaluates to the following array: The second array is generated with this expression: The result of D5:D9=“x” is an array of TRUE FALSE values like this: The double negative (–) converts these TRUE FALSE values to 1s and 0s: So, inside SUMPRODUCT we have: The SUMPRODUCT function then multiplies corresponding items in each array together: and returns the sum of products, 168 in this case....

Note: BYCOL is a beta function available only through the Insiders channel of Excel 365. The BYCOL function takes two arguments: array and lambda. Array is the array or range to process. Lambda is the LAMBDA function that should be run on each column in array. The lambda function must return a single result for each column, or BYCOL will return a #CALC! error. Examples To sum each column in a range, you can use the BYCOL function like this: The BYCOL function delivers the contents of range one column at a time to the LAMBDA function, which uses the SUM function to calculate a total for each column....

When to use Excel COLUMNS Function Excel COLUMNS function can be used when you want to get the number of columns in a specified range or array. What it Returns It returns a number that represents the total number of columns in the specified range or array. Syntax =COLUMNS(array) Input Arguments array – it could be an array, an array formula or a reference to a contiguous range of cells....