Posts

Note: values hardcoded for readability only. rate - The interest rate per period. We divide 5% by 12 because 5% represents annual interest. nper - the total number of payment periods for the loan, 60. pv - The present value, or total value of all payments now, 30000. start_period - the starting period for a given year. end_period - the ending period for a given year. In the range F5:F9, here are the formulas used: Note many values could be picked up directly with cell references, but are hardcoded in this example for readability....

As the formula is copied down, it returns TRUE if a value contains a number and FALSE if not. See below for an alternate formula based on the SEQUENCE function. FIND function The FIND function is designed to look inside a text string for a specific substring. If FIND finds the substring, it returns a position of the substring in the text as a number. If the substring is not found, FIND returns a #VALUE error....

In the example shown, the formula in C5 is: This formula returns TRUE when B5 contains any of the words in the named range inc and none of the words in the named range exc. This is an array formula and must be entered with Control + Shift + Enter. If both conditions are TRUE, the formula returns TRUE. If either condition is FALSE, the formula returns FALSE. The test for multiple words is done using the SEARCH function with help from COUNT....

This highlights values in D5:D14 that are greater than C5:C14. Note that both references are mixed in order to lock the column but allow the row to change. The rule is applied to the entire range D5:G14. The formula uses the greater than operator (>) to evaluate each cell in D5:D14 against the corresponding cell in C5:C14. When the formula returns TRUE, the rule is triggered and the highlighting is applied....

Background “Julian date format” refers to a format where the year value of a date is combined with the “ordinal day for that year” (i.e. 14th day, 100th day, etc.) to form a date stamp. There are several variations. A date in this format may include a 4-digit year (yyyy) or a two-digit year (yy) and the day number may or may not be padded with zeros to always use 3 digits....

Where data is an Excel Table in the range B5:D15. The result is two, since there are 2 males not in Group A or B. Note: You can also solve this problem with the COUNTIFS function, as explained below. The SUMPRODUCT formula is more advanced, but it scales better when there many exclusions. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?...

where “allowed” is the named range D5:D11. explained in detail here. The result is an array like this: which goes into MATCH as the lookup value. For lookup array, we use the named range “allowed”, concatenated to an empty string (""): The concatenation converts any numbers to strings, so that we are matching apples-to-apples. The result is an array like this: The last argument in MATCH, match_type is set to zero to force an exact match....

This allows a user to select a city from a short list of options by default, but also provides an easy way to view and select a city from a longer list of cities. Note: I ran into this formula and approach on the excellent Chandoo site. In this formula, the IF function is configured to test the value in cell C4. When C4 is empty or contains any value except “See full list”, the user sees a short list of cities, provided in the named range short_list (E6:E13):...

In this case, Excel extracts the month from the date in cell B6 as numbers, then concatenates them together to form a text string. The same thing is done with the date in cell C6, the month and year are extracted and joined together. Finally, the formula then tests for equivalency using the equal sign. Both dates are in January 2000, so the formula is solved as follows and returns TRUE....

Fixed time If you need to insert the current date and time in a way that will not change, use the keyboard shortcut Ctrl + ’ This shortcut will insert the current time in a cell as a value that will not automatically change. If you need to insert both the date and time in a cell, use the keyboard shortcut Ctrl + ; Then enter a space character and press Ctrl + Shift + ;...

In the most common usage, INDEX takes three arguments: array, row_num, and col_num. Array is the range or array from which to retrieve values. Row_num is the row number from which to retrieve a value, and col_num is the column number at which to retrieve a value. Col_num is optional and not needed when array is one-dimensional. In the example shown above, the goal is to get the diameter of the planet Jupiter....

The MIN function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MIN ignores empty cells, text values, and the logical values TRUE and FALSE. Basic example The MIN function returns the smallest numeric value in supplied data: When given a range, MIN returns the smallest value in the range:...

In the example shown, the formula in D5 is: where “points” is the named range C5:C12. Controlling rank order The rank function has two modes of operation, controlled by the order argument. To rank values where the largest value is ranked #1, set order to zero (0), or omit the order argument. To rank values so that the smallest value receives rank #1, set order to 1. For example, with the values 1-5 in the range A1:A5: Set order to zero (0) when you want to rank something like top sales where the largest sales numbers should get the best rank, and set order to one (1) to rank something like race results where the shortest (fastest) times should rank highest....

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.

If the clipboard contains formatted text, the formatting will be removed If the clipboard contains a formula, the formula will be removed In versions of Excel that don’t have this shortcut, you can use a two-step process: Note: this feature is currently beta only, available to Beta Channel users running Version 2210 (Build 15726.20000) or later in Excel for Windows. A dedicated Mac shortcut is expected in the future. The official announcement is here....

On Windows, you can sometimes use Ctrl+Alt+Tab to indent and Ctrl+Alt+Shift+Tab to un-indent. However, The application switcher in Windows 7 seems to conflict with these shortcuts. On the Mac, Ctrl+M and Cmd+Shift+Tab also work. Cmd+M should work, but it conflicts with the the Finder minimize window shortcut (see http://apple.stackexchange.com/questions/73886/disable-command-m-to-minimize-window) 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....

Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula, as in the screen below: The VLOOKUP formula is correct, why no result? This can be very confusing, and you might think you’ve somehow broken your spreadsheet....

As data is entered into a worksheet, Excel makes a “best guess” on the type of content it is, and formats the value automatically. By default, text values are aligned on the left, and numeric values (including dates and times) are aligned on the right. This alignment quickly shows which values Excel considers text. Text values in formulas In a formula, text values require special treatment, and must be enclosed in double quotes ("")....

with these inputs, the YIELDMAT function returns 0.081 which, or 8.10% when formatted with the percentage number format. Entering dates In Excel, dates are serial numbers. Generally, the best way to enter valid dates is to use cell references, as shown in the example. If you want to enter valid dates directly inside a function, the DATE function is the best approach. Basis The basis argument controls how days are counted....

with the following named ranges: “search” = D5, “ct” = D8, “data” = B5:B55. Note: this is an array formula, but it does not require control + shift + enter, since AGGREGATE can handle arrays natively. Almost all of the work is in figuring out and reporting which rows in “data” match the search string, and reporting the position of for each matching value to INDEX. This is done with the AGGREGATE function configured like this: The first argument, 15, tells AGGREGATE to behave like SMALL, and return nth smallest values....