Posts

where data (C5:G14), days (B5:B14), and codes (C4:G4) are named ranges. Note: In the latest version of Excel you can also use the FILTER function, as explained below. SUMPRODUCT function In the the example shown, the formula in J6 is: where data (C5:G14), days (B5:B14), and codes (C4:G4) are named ranges. In this case, we are multiplying all values in the named range data by two expressions that “zero out” values that should not be included in the final sum: The first expression applies a condition based on codes: Since J4 contains “B”, the expression creates an array of TRUE FALSE values like this: Each TRUE indicates a column where the code is “B”....

The formula returns the surface area of a cone with radius given in column B and height given in column C. Units are indicated generically with “u”, and the resulting area is units squared (u2). The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function, which returns the number 3.14159265358979, accurate to 15 digits: To square a number in Excel, you can use the exponentiation operator (^): To get the square root of a number, you can use the SQRT function: Rewriting the formula for surface area of a cone with Excel’s math operators, we get: Following Excel’s order of operations, exponentiation will occur before multiplication....

This also means that most of the time, we work with cells and ranges, worksheets, workbooks, and other objects which are a part of the Excel application. But VBA is a lot more powerful and can also be used to work with stuff outside of Excel. In this tutorial, I will show you how to use VBA FileSystemObject (FSO) to work with files and folders on your system or network drives....

Here we have a simple summary of sales by quarter in 4 regions, with totals for each month, and quarterly totals as well. Now, if I select any cell in the data, and insert a chart, Excel includes the totals, which you probably don’t want. So let’s go over some ways to exclude this data. First, you can always exclude data is when the chart is created. If I make a selection that excludes monthly totals, and then insert a chart, they aren’t plotted....

Datasets with people’s names usually have the first and last name in one cell, designated by a space character. Although this isn’t necessarily bad, it can be more helpful to have the first and last names in different columns. This way you can more easily perform further analysis on your data such as sorting and filtering based on just the last name. This post will show you all the ways to extract the first and last names into different cells in Excel!...

In the example shown, the formula in D6 is: This is what happens at the core of the formula here: Here are are simply subtracting an earlier date from a later date. To figure out what dates to use for each date range comparison, we use MIN to get the earliest end date, and MAX to get the latest end date. We add 1 to the result to make sure we are counting “fence posts” and not “gaps between fence posts” (analogy from John Walkenbach in the Excel 2010 Bible)....

where things is the named range E5:E9. Working from the inside out, this formula is based on the formula described here, which uses the SEARCH function together with the ISNUMBER function: In a nutshell, SEARCH returns a number if it finds the target string and an error if not, and ISNUMBER converts this result into a TRUE or FALSE. In this case, we are looking for several strings at once – the five colors in the named range things (E5:E10), so the code above returns an array with five TRUE FALSE values like this: Notice the TRUE values correspond to white and green in the list of colors (positions 3 and 5), and FALSE values correspond to the colors not found....

A chart layout is a specific set of chart elements arranged in a particular way. Excel provides a limited set of chart layouts on the ribbon in the Quick layout menu on the Design Tab under Chart Tools. When you access this menu, you’ll see available layouts. As you hover over each layout, you’ll see a preview of the layout applied to the chart. To apply a layout, just click....

where data is the named range B5:B15. The result is 7, because there are seven cells in the range B5:B15 that contain “blue” or “green”. This formula is case-sensitive because the FIND function is case-sensitive. Note: It is also possible to use a simpler formula based on a helper column, as explained below. Note: The main formula described on this page is case-sensitive because the FIND function is case-sensitive. If you want a formula that is not case-sensitive, you can substitute the SEARCH function for the FIND function....

As the formula is copied down, it returns a count of each color in column B. This formula uses the full column reference B:B for convenience. You can also use an absolute reference like $B$5:$B$16. An Excel Table would also be a good option. See also: Running count of occurence in list, and Summary count with COUNTIFS COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria....

where data is the named range B5:B16, and each timestamp includes both a date and time. See below for the formulas used to generate the summary table in E7:F9. Basic count To get a count of individual dates that occur in B5:B16, ignoring time values, the formula in F4 is: Working from the inside out, the INT function is used to remove the time values from the timestamps like this: This works because Excel dates are just serial numbers and Excel times are fractional dates that appear as decimal values....

Syntax COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2,…) Range 1 (required) – This is the range to which the first criteria is applied.Criteria 1 (required) – This is the criteria Range 1 has to satisfy to be included in the count.Range 2, Range 3,… (optional) – These are more criteria ranges that can be used.Criteria 2, Criteria 3,… (optional) – These are more criteria to which the associated range has to satisfy to be included in the count....

When a different ID is typed into cell G5, the VLOOKUP function retrieves a new name and points from the data in B5:D16. Note: the LET function is new in Excel 365. It’s part of the new dynamic array functions in Excel. If points are over 300, the message is extended: In the article below, we’ll build up the formula step-by-step, then streamline the final formula with the LET function....

“Array of arrays” is not related to the TEXTSPLIT function per se, but TEXTSPLIT is an easy way to see the limitation in action. When TEXTSPLIT is used on a single cell, it returns the text split by delimiter in a single array whose values spill onto the worksheet into multiple cells. However, when you call TEXTSPLIT on a range of cells, TEXTSPLIT returns an array of results where each result is itself an array, creating an “array of arrays”....

ISOWEEKNUM takes just one argument, date, which must be a valid Excel date. Examples In the example shown, the formula in D5, copied down, is: The formula in E5, copied down the table, is: By default the standard WEEKNUM function will start week number 1 on the first day of the year, then increment week numbers on Sundays after that. The ISOWEEKNUM function increments on Mondays, and starts week 1 on the first week that contains a Thursday....

Example In the example shown, we want to calculate the yield of a bond with a settlement date of 1-Feb-2019. The bond matures on 15-Feb-2022, and pays a coupon rate of 5%, with the first coupon paid on 15-Feb-2019. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is: With these inputs, the ODDFYIELD function returns a yield of 6....

Excel has a Quick Access Toolbar that gives you instant access to your favorite/important options. However, not many people use the QAT efficiently. Last month I have asked 15 excel users about it, and only 7 were using it on frequent days. And, others were busy navigating Excel ribbon for their stuff. In this article, I will share my top 5 QAT options that help me stay productive and save time....

The RANDBETWEEN function takes two arguments: bottom and top. Bottom represents the lower bound for a random number, and top represents the upper bound. RANDBETWEEN includes both top and bottom values in the range of integers that may be returned. Examples Below are basic examples of RANDBETWEEN formulas: Multiple results To generate multiple random numbers in multiple cells, select the target cells, enter the RANDBETWEEN function, and press control + enter to enter the same formula in all cells at once....

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.