where “countries” is the named range B4:B13 Note: in Excel 365, the SORT and SORTBY functions make the approach here unnecessary. Inside COUNTIF, the range argument is supplied as the named range “countries” (B4:B13), and criteria is supplied as “less than or equal to” the value in C5. In each row, COUNTIFS returns the number of values that are less than or equal to the current value, which creates a sequential list of numbers (i....
For this example, we want to calculate the number of payments for a $5000 loan, with a 4.5% interest rate, and fixed payments of $93.22. The NPER function is configured as follows: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: pmt - The payment made each period. This is the known amount $93.22, which comes from cell C7....
where group (B5:B15), color1 (C5:C15), and color2 (D5:D15) are named ranges. In this example, the goal is to count rows where group = “a” AND Color1 OR Color2 are “red”. This means we are working with scenario 2 above. With COUNTIFS You might at first reach for the COUNTIFS function, which handles multiple criteria natively. However, the COUNTIFS function joins conditions with AND logic, so all criteria must be TRUE to be included in the count: This makes COUNTIFS unworkable, unless we use multiple instances of COUNTIFS: Translation: count rows where group is “a” and color1 is “red” + count rows where group is “a” and color2 is “red”....
Manually with ^ The cube root of a number can be calculated manually by raising a number to the (1/3) using the exponentiation operator (^). In the example shown, the formula in C5 is: Be sure to enclose 1/3 in parentheses to control the order of operations. With the POWER function The cube root of a number can also be calculated with the POWER function, by supplying 1/3 as the power argument....
To allow a user to enter only dates that are weekdays (i.e. Monday, Tuesday, Wednesday, etc.) you can use data validation with a custom formula based on the WEEKDAY function. In the example shown, the data validation applied to C5:C7 is: This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the return_type argument for WEEKDAY is provided as 2....
where xlist is the named range D5:D11. In this case, we have previous defined the named range “xlist” as D5:D11. This range holds characters that are not allowed. The formula we are using for data validation is: Working from the inside out FIND function is configured with xlist for “find text”, and cell B5 as the text to search. Because we are giving FIND an array with multiple values, FIND returns an array of result, one for each character in the named range “xlist”....
where “data” is the named range C5:G9. Inside the sum function, the first reference is simply the first cell in the range that covers all possible cells: To get the last cell, we use INDEX. Here, we give INDEX the named range “data”, which is the maximum possible range of values, and also the values from J5 (rows) and J6 (columns). INDEX doesn’t return a range, it only returns a single cell at that location, E9 in the example: The original formula is reduced to: which returns 300, the sum of all values in C5:E9....
Boolean logic can be used to simply formulas and eliminate the branching seen in nested IF formulas. In the example the formula in E5 is equivalent to: Boolean logic in other functions Boolean logic often shows up inside other formulas and functions. The SUMPRODUCT function is a good example. To count cells in A1:A10 that are equal to “red” with SUMPRODUCT, you can use a formula like this: The double negative coerces the TRUE and FALSE values that result from the expression A1:A10=“red” to 1s and 0s, and SUMPRODUCT then returns the sum of these numbers, which corresponds to the count of cells that contain “red”....
The CLEAN function accepts just one argument, text, which can be a text string or number. CLEAN removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31), if any are found, and returns the result. Text without these characters is returned unchanged. Note that CLEAN will remove line breaks if found. CLEAN will not remove extra space characters. To remove extra space, use the TRIM function....
The exponential function models exponential growth and has the unique property where the output of the function at a given point is proportional to the rate of change of the function at that point. The inverse of the exponential function is the natural logarithm which represents the opposite of exponential growth, exponential decay. For a more detailed explanation see wumbo.net. Notes e stands for Euler’s number. The number e is a famous irrational number, and one of the most important numbers in mathematics....
Example In the example shown, we want to find the amount received at maturity for a bond with an initial investment of $1000 and a discount rate of 4.25%. The settlement date is 6-Jul-2017 and the maturity date is 15-Jan-2020. There are no periodic interest payments, and the day count basis is US (NASD) 30/360. The formula in F5 is: With these inputs, the RECEIVED function returns $1,120.21 , with currency number format applied....
With a laptop keyboard, use Control Shift +. With a full keyboard, use Control + Note: In Mac 2016, this shortcut was changed to Command Shift +, and now Control + I changes text to italic. In previous Mac Excel versions, Control + I inserts a row (when a row is selected) or brings up the Insert Cells dialog when the selection does not include an entire row. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
Right click the status bar to control what kind of information is displayed. 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.
When the expression above returns TRUE for a given cell in the calendar, the blue fill is applied. Note: this is a variation on the by day Gantt chart example here. This makes it easy to set up a conditional formatting rule that compares the date associated with each column with the dates in columns B and C. The formula is based on the AND function, configured with two conditions....
returns “$A$1” as text. The INDIRECT function returns a valid reference from a text string. In the example shown, the ADDRESS function returns the value “$C$9” inside INDIRECT: The INDIRECT then this text into a normal reference and returns the value in cell C9, which is “Peach”. Note: INDIRECT is a volatile function and can cause performance problems in more complicated worksheets. With INDEX By feeding the INDEX function an array that begins at A1, and includes cells to reference, you can get the same result with a formula that may be easier to understand....
where “lower” is the named range H5 and “upper” is the named range H6. Note: this example was inspired by a formula challenge on Chandoo, and a more complete solution provided by formula master Barry Houdini on the MrExcel forum. The logic of the formula is to calculate all possible working hours between the start and end dates, inclusive, then back out any hours on the start date that occur between the start time and lower time, and any hours on the end date that occur between the end time and the upper time....
The result is a chart that shows a proportional breakdown of each quarter by region. Looking at the chart, you might wonder how to show the actual percentages in each bar? This isn’t hard to do, but it does take a little prep work. Unlike a pie chart, which has a specific option to show percentages, a 100% stacked chart does not have this option. But there is an option to pull values from other cells....
In this worksheet we have a list of six generations. Each generation has a start year and end year, which represent birth years. How can we plot these generations in an easy-to-read bar chart? This seems like a simple problem, but a solution takes a little creative thinking. To help illustrate the challenge, I’ll plot this data in the most obvious way. I want a horizontal bar chart, so I’ll use the icon next to recommended charts....
A scatter plot or scatter chart is a chart used to show the relationship between two quantitative variables. A scatter chart has a horizontal and vertical axis, and both axes are value axes designed to plot numeric data. On this worksheet, we have the height and weight for 10 high school football players. Let’s create a scatter plot to show how height and weight are related. When creating scatter charts, it’s generally best to select only the X and Y values, to avoid confusing Excel....
Video: Spilling and the spill range Spill behavior is native It’s important to understand that spill behavior is automatic and native. In Dynamic Excel (Excel 365/2021) any formula, even a simple formula without functions, can spill results. Although there are ways to stop a formula from returning multiple results, spilling itself can’t be disabled with a global setting. Similarly, there is no option in Excel to “disable #SPILL errors. To fix a #SPILL error, you’ll have to investigate and resolve the root cause of the problem....
