NA Function
The Excel NA function returns the #N/A error. #N/A means "not available" or "no value available". You can use the NA function to display the #N/A error when information is missing.Read more
The Excel NA function returns the #N/A error. #N/A means "not available" or "no value available". You can use the NA function to display the #N/A error when information is missing.Read more
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards....Read more
The Excel COUNTIFS function returns the count of cells in a range that meet one or more conditions. Each condition is provided with a separate range and criteria, and all conditions must be TRUE for a cell to be included in the count. COUNTIF can be used to count cells that...Read more
An expanding reference (or expanding range) in Excel defines a range that expands as a formula is copied down or across cells. This is done by "mixing" absolute and relative references – making the first cell an absolute reference and the last cell a...Read more
The Excel DEVSQ function returns the sum of the squared deviations from the mean for a given set of data. Read more
A structured reference is a special syntax for referencing Excel Tables. Structured references work like regular cell references in formulas, but they are easier to read and understand. Structured references are also dynamic, and adjust automatically when data...Read more
Excel Tables provide a special kind of formula syntax called a structured reference. In a nutshell, structured references makes it possible to refer to parts of an Excel Table by name, so you can write formulas...Read more
This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:
type,G$4
where type is the named range D5:D14...Read more
In this example, the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses a sheet name typed into a cell to construct a reference to a range on that sheet. If the sheet name is changed, the reference should update automatically. The key to the...Read more
In this example, the goal is to test if a given range contains duplicate values and return TRUE if duplicates exist and FALSE if not. This is essentially a counting problem and the solution is based on the COUNTIF function, which counts values in a range...Read more
In this example, the goal is to count rows where the value in column one is "A" or "B" and the value in column two is "X", "Y", or "Z". In the worksheet shown, we are using array constants to hold the values of interest, but the article also shows how to...Read more
One of the trickier problems in Excel is to count rows in a set of data with "OR logic". There are two basic scenarios: (1) you want to count rows where a value in a column is "x" OR "y" (2) you want to count rows where a value, "x", exists in one column OR another.
In this example, the...Read more
This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate "2nd column", and zero as the last argument to force an exact match.
Although in this case we are mapping text values to numeric outputs, the same formula can...Read more
Boolean algebra is a mathematical system that represents logical expressions and relationships using only two values: TRUE and FALSE. Boolean logic refers to the principles that support Boolean algebra, including logical operations like AND, OR, and NOT and rules that govern...Read more
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing:
=AVERAGE(first:last)
where "first" is a...Read more
In geometry, a sphere is defined as a set of points that are all the same distance (r) from a given point in a three-dimensional space. The formula for calculating the volume of a sphere is:
Where r represents radius, and the Greek letter π ("pi")...Read more
Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a range to ISNUMBER (i.e. an array), ISNUMBER will return an array of results. In the example, the...Read more
In this example, the goal is to count numbers that fall within specific ranges. The lower value comes from the "Start" column, and the upper value comes from the "End" column. For each range, we want to include both the lower value and the upper value. For convenience, the numbers being...Read more
The Excel FVSCHEDULE function returns the future value of a single sum based on a schedule of given interest rates. FVSCHEDULE can be used to find the future value of an investment with a variable or adjustable rate.Read more
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.Read more
Excel supports both full column, and full row references. A full row reference is a reference that refers to an entire row. For example, to sum all of the values in row 1, you can use a formula like this:
...Read more
This article is for those of you who don't get pivot tables. Maybe you tried pivot tables once, and didn't see what the big deal was, or maybe you got frustrated when a pivot table wouldn't behave. They can be that way.Read more
The Excel NOT function returns the opposite of a given logical or Boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.Read more
In this example, the goal is to extract the first 3 values or the last 3 values from the named range data (B5:B15). We also want to exclude any empty cells from our results. In the worksheet shown the formula in cell D5 is:
=INDEX(FILTER(
...Read more