Posts

Example 1 | Example 2 | Example 3 | Example 4 | More examples Variables are named and assigned values in pairs, separated by commas (name1,value1, name2,value2, etc). LET can handle up to 126 name/value pairs, but only the first name/value pair is required. The scope of each variable is the current LET function, and nested functions below. The final result is a calculation or a variable previously calculated. The result from LET always appears as the last argument to the function....

Examples The NA function returns the #N/A error: You can use the NA function in other formulas. For example, in the formula below, the IF function is configured to test if cell A1 is empty. If so, IF returns NA(), which returns the #N/A error. If A1 is not empty, IF returns A1*B1: You can use the NA function to indicate missing information. In the worksheet shown above, cells C9 and C13 contain the NA function: This indicates that cost is not available....

NORM.S.DIST expects Standardized Input NORM.S.DIST expects standardized input in the form of a z-score value. A z-score value represents how far a value is from the mean of a distribution in terms of the standard deviation of the distribution. To calculate the z-score, subtract the mean from the value and then divide by the standard deviation or use the STANDARDIZE function as shown in the two formulas below: Note, see the NORM....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

In the example shown, the formula used to highlight duplicate values is: Both ranges were selected at the same when the rule was created. The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function: COUNTIF will either return zero (evaluated as FALSE) or a positive number (evaluated as TRUE) for each value in both ranges. If both counts are positive (i....

Recently, I was working on setting the agenda for a meeting. I had a list in Excel where I had a list of people and the dates on which they acted as the ‘Meeting Chair’. Since there was repetition in the list (which means that a person has been Meeting Chair multiple times), I also needed to know when was the last time a person acted as the ‘Meeting Chair’....

In the example shown, the formula in C5 is: Working from the inside out, we use the SUBSTITUTE function to find all line breaks (char 10) in the text, and replace each one with 200 spaces: After the substitution, the looks like this (with hyphens marking spaces for readability): With 200 spaces between each line of text. Next, the RIGHT function extracts 200 characters, starting from the right. The result will look like this: Finally, the TRIM function removes all leading spaces, and returns the last line....

In the example, the formula cell B4 of the helper column looks like this: This formula picks up the value in D4 and uses concatenation to add a hyphen, and the result of a COUNTIF function. The COUNTIF function uses an expanding range (the mixed reference $D$4:D4) to generate a running count of the id in the data. On the lookup side, VLOOKUP is used to fetch values form the table, taking into account the “nth” occurrence....

The result is a decimal value with percentage number format applied. However, since the price change is not in the table as a separate column, we need to add a step: Applying this approach to the worksheet as shown, the formula in cell E5, copied down, is: For each item in the table, Excel returns a calculated percentage. Formatting percentages in Excel In mathematics, a percentage is a number expressed as a fraction of 100....

Given a date, what formula will highlight other dates in the same month and year? What formula would you use to apply conditional formatting? Please post your answers in the comments section below. Like so many things in Excel, there are many ways to solve this problem. Need some ideas or inspiration? You can find Excel’s date functions here. There’s a practice file attached below so you can easily try out your ideas....

If you want to highlight duplicate rows in an unsorted set of data, and you don’t want to add a helper column, you can use a formula that uses the COUNTIFS function to count duplicated values in each column of the data. For example, if you have values in the cells B4:D11, and want to highlight entire duplicate rows, you can use rather ugly formula: Named ranges for a cleaner syntax The reason the above formula is so ugly is that we need to fully lock each column range, then used a mixed reference to test each cell in each column....

Let’s take a look. Here we have a simple table meant to summarize the features of a hypothetical service plan. One thing you might want to do with a table like this is combine cells with like values. For example, Feature Y is not offered in Plans A, B, or C, and feature Z is not applicable to Plan A or B. So, we could combine these cells to visually simplify the layout....

Table styles control the look and feel of an Excel Table. Tables styles allow you to format an entire table with a single click, and the style is applied continuously to a table as new rows and columns are added. You can find table styles listed on the Design tab of the Table Tools ribbon, whenever you have at least one cell in a table selected. You’ll see a large number of pre-built styles organized in several categories....

So, the first step is going to be to calculate the number of characters in the cell. We’ll do that with the Length or LEN function. And then, we need to figure out the number of characters without spaces. To get the number of characters without spaces we need to strip the spaces out. I’ll use SUBSTITUTE, and we’ll point at this text here. We’ll look for spaces and replace that with “nothing....

Example Generic Formula What It Does This formula will calculate the volume of a sphere given its radius. How It Works The volume of a sphere is given by the above formula where π is a constant approximately equal to 3.14159265 and r is the radius. Excel has this constant built in as a function with no parameter inputs PI(). The POWER function will take any number and raise it to the power of any other number....

Below is a detailed written tutorial about Excel Data Entry form in case you prefer reading over watching a video. Excel has many useful features when it comes to data entry. And one such feature is the Data Entry Form. In this tutorial, I will show you what are data entry forms and how to create and use them in Excel. Why Do You Need to Know About Data Entry Forms?...

Both – a line chart and an area chart – show a trend over time. In case you’re only interested in showing the trend, using a line chart is a better choice (especially if the charts are to be printed). For example, in the below case, you can use a line chart (instead of an area chart) Area charts are useful when you have multiple time series data and you also want to show the contribution of each data set to the whole....

People have been doing this since early versions of Excel and creating relationships between different tables using VLOOKUP or INDEX and MATCH. The good news is that more and more database-like functionality is being added with each version, and since Excel 2013 we’ve been able to add relationships to tables. This example has an Orders table that contains data related to customer orders such as the Customer ID, Product Sold, and the Total....