Let’s take a look. The COUNTIF function counts cells that satisfy a single condition that you supply. It takes two arguments: range and criteria. For example, if I want to count the cells in this range that contain the number “15,” I enter B7:B12 for the range, and “15” for the criteria. Excel then returns “1,” since only one cell contains “15”. If I temporarily enter another “15” that result will change....

The results in column E are decimal values with the percentage number format applied. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns a new price for each item in the table, based on the percentages shown in column D. Negative percentages Negative percentages will have the effect of decreasing the original price. For example, with -10% in cell D5 (-0....

Note: the new TEXTJOIN function is a better way to solve this problem. See below for more information. This part of the formula is annoyingly manual. To speed things up, copy &" “& to the clipboard before you start. Then follow this pattern: [click] [paste] [click] [paste] [click] [paste] until you get to the last cell reference. It actually goes pretty fast. The result of this concatenation (before TRIM and SUBSTITUTE run) is a string like this: Next, the TRIM function is used to “normalize” all spacing....

This formula splits the text in B5 using a comma (",") as the delimiter. The result is a horizontal array that spills into columns D through H. The delimiter is provided as the second argument to the function, and can be changed to suit the situation. Note: This is an interesting and useful LAMBDA example, but Excel 365 now provides the TEXTSPLIT function, which makes this workaround unnecessary in newer versions of Excel....

Unlike the formula explained here, this formula is not recursive. The formula takes four inputs: text - the incoming text chars - the characters to strip rep - the character to replace stripped characters with keep - strip or preserve chars (FALSE = strip, TRUE = preserve) The keep parameter is a boolean that “flips” the behavior of the function from stripping characters to preserving characters. In a nutshell, the SEQUENCE function is used to generate two arrays: one for the text, one for chars....

Pros Simple presentation; easy to read and create Clean presentation of multiple data series with many data points Good for showing trends over periods of time Can handle positive and negative values Cons Harder to read when lines overlap frequently Line can imply more data than actually available (compared to bar or column chart) …Read more Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

where “files” is the named range B5:B11. Context In this example, we have a number of file versions listed in a table with a date and user name. Note that file names are repeated with a counter at the end as a revision number - 001, 002, 003, etc. Given a file name, we want to retrieve the name of the last or latest revision. There are two challenges: To overcome these challenges, we need to use some tricky techniques....

You can also express 15 minutes in a formula with this formula: The formula above divides 15 by 1440, which is the number of minutes in one day. So, to Excel, these formulas are identical: Round to other time intervals As you would expect, you can use the same formula to round to different time intervals. To round to the nearest 30 minutes, or nearest 1 hour, use these formulas...

where “data” is the named range B5:B13. which is explained in more detail here. If the data contains all text values, or all numeric values, the rank will be correct. However, if the data includes both text and numbers, we need to “shift” the rank of all text values to account for the numeric values. This is done with the second part of the formula here: Here, we use the COUNT function to get a count of numeric values in the data, then multiply the result by the logical result of ISTEXT, which tests if the value is text and returns either TRUE or FALSE....

Bessel’s correction, STDEV.P vs. STDEV.S When you calculate statistics for an entire population (mean, variance, etc.) results are accurate because all data is available. However, when you calculate statistics for a sample, results are estimates and therefore not as accurate. Bessel’s correction is an adjustment made to correct for bias that occurs when working with sample data. It appears in formulas as n-1, where n is the count. When working with a sample population, Bessel’s correction can provide a better estimation of the standard deviation....

Note: the point of this formula is to eliminate the manual task of entering ranges manually with a single global formula, at the cost of a more complex formula. The arguments for OFFSET are provided as follows: For reference we use the first cell in the data range, B5, entered as a mixed reference (column locked, row relative). For rows, we use 0, since we don’t need to change rows....

With the number 3 in cell K2 for n, the result is 17. As the formula is copied down, we get a new sum on each row. Each result is the sum of every third value from column B to column J. Note: With a small set of data, you can easily enter a formula manually to sum columns as you like. The point of this example is to show how to create a general solution that will scale up to a larger set of data, and allow the value for n to be easily changed....

Donate If you would like to support Exceljet directly, you can make a secure, one-time donation here: Donate Thank you for your support - we appreciate it! Dave and Lisa

One of the new functions that comes with the dynamic array version of Excel is UNIQUE. The UNIQUE function lets you extract unique values in a variety of ways. The UNIQUE function takes three arguments. The first argument, array is the source data you are working with. The second argument, by_col means by column. Use this when data should be compared across columns instead of rows. The last argument, exactly_once, tells UNIQUE to return values that only appear once in source data....

Pros Quick visualization of hierarchical data At-a-glance breakdown data in categories Cons Data must be sorted by category Missing many of the options available in other chart types Author 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.

which calculates the area of a parallelogram given the base in column B, the height in column C. In Excel, the same formula can be represented like this: So, for example, to calculate the area of a parallelogram where b is 5, and h is 4, you can use a formula like this: In the example shown, the goal is to calculate the area for eleven parallelograms using the base value in column B and the height value in column C....

Note: the complexity of the formulas on this page is a result of times that cross midnight. You can dramatically simplify things by using date values that contain times (“datetimes”), as explained below. Simple duration calculation When start time and end time occur in the same day, calculating duration in hours is straightforward. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula: To see the result in hours and minutes, apply a time number format like this: However, when times cross a day boundary (midnight), things can get tricky....

This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%. Simple interest means that interest payments are not compounded – the interest is applied to the principal only. In the example shown, the formula in C8 is: So, using cell references, we have: 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....

This formula counts non-blank cells in a range that begins at B5 and ends 2 rows above the cell where the formula lives. The same formula is copied and pasted 2 rows below the last entry in the data as shown. Working from the inside out, the work of setting up a variable range is done by the OFFSET function here: OFFSET has five arguments and is configured like this:...