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....
Syntax LOWER(Text) Text (required) – This is the text string you want to turn into all lower case letters. Example In this example we convert some uppercase text into lower case.
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.
While the impact on the performance may be negligible when there is less data, it becomes more profound as you add more and more data/calculations to the workbook. 9 out of 10 times, an Excel user would complain about the slow Excel spreadsheets. And there is hardly anything you can do about it. Well, that’s NOT completely true. The way Excel has been made, it does get slow with large data sets....
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....
Comments in VBA could be really useful for beginners, where you can add a comment to a line of code (or a block of code) that explains what it does. So the next time you come back to the code, you’ll not be completely lost and would have some context because of the comments. Even for advanced Excel VBA programmers, once the code starts to get beyond a few lines, it’s a good idea to add context using comments (especially if there is a chance that someone else may have to work on the code in the future) And since it’s a comment, VBA ignores it while executing the code....
In some cases, you may need to calculate the total number of characters in a cell/range or the number of times a specific character occurs in a cell. While there is the LEN function that can count the number of characters in a cell, you can do the rest as well with a combination of formulas (as we will see later in the examples). In this tutorial, I will cover different examples where you can count total or specific characters in a cell/range 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:...
In the example shown, the formula in C5 is: The CHOOSE function simply maps numbers to values. The first argument is the number to map, and subsequent arguments represent associated values. In this case, 7 values have been provided in the order required to work with WEEKDAY’s Sunday through Saturday scheme. With a date from column B, WEEKDAY returns a number which is fed to the CHOOSE function. CHOOSE returns the value at that position in the list of abbreviations....
In the example shown, the data validation applied to B5:B9 is: where ids is the named range B5:B9. The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need two conditions: Logical 1 tests if the input is a number using the ISNUMBER function: The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. Logical 2 tests checks that the input doesn’t already exist in the named range “ids”: COUNTIF returns a count of the value in B5 inside the named range ids (B5:B9)....
