Posts

Note: this is an array formula and myst be entered with control + shift + enter. Results in an array containing seven values: Each number in the array is the result of subtracting the “low” from the “high” in each of the seven rows of data. This array is returned to the MAX function: And MAX returns the maximum value in the array, which is 32. MIN change To return the minimum change in the data, you can substitute the MIN function for the MAX function: As before, this is an array formula and myst be entered with control + shift + enter....

For each value which we want to replace, we have to create a Replace Values step in the query. If we have tens or even hundreds of values to replace, then it can be quite tedious. If we already have a list of values which we need to replace, then it would be easier if we could do it all in one step based on the list! In this post we’re going to learn how to do a bulk find and replace in power query based on a list of values....

For example, suppose you have the sales data for different regions (East, West, North, and South). You can combine this data from different workbooks into a single worksheet using Power Query. If you have these workbooks in different locations/folders, it’s a good idea to move all these into a single folder (or create a copy and put that workbook copy in the same folder). So to begin with, I have four workbooks in a folder (as shown below)....

If B5 or C5 or D5 is blank, the formula returns TRUE and triggers the rule. Note: conditional formatting formulas should be entered relative to the “active cell” in the selection, which is assumed to be E5 in this case. As the formula is evaluated, formula references change so that the rule is testing for blank values in the correct row for each of the 10 cells in the range: If any cell in a corresponding row in column B, C, or D is blank, OR function returns TRUE and the rule is triggered and the green fill is applied....

Note the text string is always 9 characters long, and each component is 2 digits. The core of this formula is the TIME function, which assembles a valid time using individual hour, minute, and second components. Since these values are all together in a single text string, the MID function is used to extract each component: The results are fed directly to the TIME function as arguments. The code below shows how the formula is solved in cell F5: Notice MID, as a text function, returns text instead of actual numbers....

The result is 4, since there are four cells in the range D5:D16 that contain “red”. COUNTIF function One way to solve this problem is with the COUNTIF function, which is designed to count cells in a range that meet one specific condition. COUNTIF takes two arguments: range and criteria: For this problem, range is D5:D16 and criteria is “red”. We place double quotes (“red”) because “red” is a text value....

For example, consider the following examples: There are multiple ways to skin this data cat in Excel. The method you choose to delete the rows will depend on how your data is structured and what’s the cell value or condition based on which you want to delete these rows. In this tutorial, I will show you multiple ways to delete rows in Excel based on a cell value or a condition....

The ARRAYTOTEXT function takes two arguments: array and format. Array is the array or range to convert to text. Array can be provided as a range like A1:A3 or an array like {1,2,3}. The format argument controls the structure of the output. By default, format is zero and ARRAYTOTEXT will output a “concise” format that displays plain unquoted values and no curly braces. Essentially, concise format is a plain, human-readable format....

Examples Use the COLUMNS function to get the column count for a given reference or range. For example, there are 6 columns in the range A1:F1 so the formula below returns 6: The range A1:Z100 contains 26 columns, so the formula below returns 100: You can also use the COLUMNS function to get a column count for an array constant: Although there is no built-in function to count the number of cells in a range, you can use the COLUMNS function together with the ROWS function like this: More details here....

When to Use Excel DAY Function Excel DAY function can be used when you want to get the day value (ranging between 1 to 31) from a specified date. What it Returns It returns a value between 0 and 31 depending on the date used as the input. For example, for the month of February, it will return a day value between 0 and 29. Syntax =DAY(serial_number) Input Arguments serial_number: It is the serial number of the date for which you want to get the day value....

Excel’s DURATION function returns the Macauley duration for an assumed par value of $100. The Macaulay duration is the weighted average term to maturity of the cash flows from a security. The weight of each cash flow is determined by dividing the present value of the cash flow by the price. Excel also provides the MDURATION function for calculating modified duration. Example In the example shown, we want to calculate the duration of a bond with an annual coupon rate of 5% and semi-annual payments....

The FLOOR function takes two arguments, number and significance. Number is the numeric value to round down. The significance argument is the multiple to which number should be rounded. In most cases, significance is provided as a numeric value, but FLOOR can also understand time entered as text like “0:15”. See the example below. FLOOR works like the MROUND function, but unlike MROUND, which rounds to the nearest multiple, FLOOR always rounds down....

The FREQUENCY function always returns an array with one more item than bins in the bins_array. This is by design, to catch any values greater than the largest value in the bins_array. The general pattern for FREQUENCY is: where data_array and bins_array are typically ranges on the worksheet. Instructions To create a frequency distribution using FREQUENCY: In Excel 365, it is not necessary to enter FREQUENCY as an array formula....

When the function screen tip is visible, you can use it to precisely navigate the function arguments in a formula. The window is context-sensitive and its contents depend on the function the cursor resides in. As you move the cursor through a formula, the currently active argument is bolded. If the cursor is moved to a different function in the same formula, the arguments for that function are displayed. If you click directly on the argument name, the text that makes up that argument will be selected....

Examples If cell A1 contains the formula =2+2, the ISFORMULA function returns TRUE: If cell A1 contains the text “apple”, the ISFORMULA function returns FALSE: Count formulas To count cells in a range that contain formulas, you can use the SUMPRODUCT function like this: The double negative coerces the TRUE and FALSE results from ISFORMULA into 1s and 0s and SUMPRODUCT returns the sum. Notes You can temporarily display all formulas in a worksheet with a keyboard shortcut....

The MINIFS function takes three required arguments: min_range, range1, and criteria1. With these three arguments, MINIFS returns the minimum number in min_range where corresponding cells in range1 meet the condition set by criteria1. Additional conditions are applied using range/criteria pairs. The second condition is defined by range2 and criteria2, the third condition is range3 and criteria3, and so on. MINIFS can handle up to 126 range/criteria pairs. When using MINIFS, keep the following in mind:...

Examples With a single cell reference, ROW returns the associated row number: When a reference is not provided, ROW returns the row number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 6: When ROW is given a range, it returns the row numbers for that range: In Excel 365, which supports dynamic array formulas, the result is an array {4,5,6} that spills vertically into three cells, starting with the cell that contains the formula....

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.