Posts

Where data is the named range B5:B15. The result is a case-sensitive count for each code in column D. EXACT function The EXACT functions sole purpose is to compare text in a case-sensitive manner. EXACT takes two arguments: text1 and text2. If text1 and text2 match exactly (considering upper and lower case), EXACT returns TRUE. Otherwise, EXACT returns FALSE: Worksheet example In the example shown, we have four codes in column D and some duplicated codes in B5:B15, the named range data....

where data is the named range B5:B16. COUNTIF returns 3, since there are three cells that begin with “apx”. Note that COUNTIF is not case-sensitive. See below for a case-sensitive formula. COUNTIF function The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument: question mark (?), asterisk(), or tilde (~). A question mark (?...

The result in C5 is 3; the total number of characters in cell B5. The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters, as you can see in cell C9. Numbers Numbers in Excel (including dates, times, currency, etc.) are often formatted with a number format. It’s important to understand that the characters that make up numbers are counted in their raw form (i....

The database argument is a range of cells that includes field headers, field is the name or index of the field to count, and criteria is a range of cells with headers matching those in database. Using the example above, you can count records where the color is “red” and price is > 10 with these formulas: Caution: If the value in a field is empty it will not be counted, even when criteria match....

For example, to convert the price “1 and 1/16” to decimal notation for pricing given to the nearest 1/16, you can use the DOLLARFR function like this: Notice the first argument is a normal decimal value. The second argument is used to indicate the denominator of the fractional multiple to use for the conversion, i.e. 8 = 1/8, 16 = 1/16, 32 = 1/32, etc. In the example shown, the formula column E, copied down, is: On each row, the DOLLARDE function picks up the decimal value from column C and the fraction denominator from column D....

To calculate predicted values, FORECAST.ETS uses something called triple exponential smoothing. This is an algorithm that applies overall smoothing, trend smoothing, and seasonal smoothing. Example In the example shown above, the formula in cell D13 is: where sales (C5:C12) and periods (B5:B12) are named ranges. With these inputs, the FORECAST.ETS function returns 618.29 in cell D13. As the formula is copied down the table, FORECAST.ETS returns predicted values in D13:D16, using values in column B for target date....

You can use use VLOOKUP to perform a lookup with multiple criteria by adding a helper column to the data. In the example above, a helper column is used to concatenate first and last names, so that VLOOKUP can be used to find department using both names. Click for details on this formula. You can use a helper column with a formula that returns a value like TRUE for rows that meet specific conditions, then use go to special and delete only those rows....

One of the more complicated examples of this syntax occurs when locking column references. Although it may not look like it, a reference to a table column is relative and will change as a formula is copied. For example this formula entered in G5: will change to: when the formula is copied to H5. How can we look a column reference to stop it from changing? Unlike a standard absolute or mixed reference, you can’t use the $ sign to lock a table column reference....

Notes Be consistent with inputs for rate. For example, for 5-year loan with 4.5% annual interest, enter the rate as 4.5%/12. By convention, the loan value (pv) is entered as a negative value. 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....

ROUND takes two arguments, number and num_digits. Number is the number to be rounded, and num_digits is the place at which number should be rounded. When num_digits is greater than zero, the ROUND function rounds on the right side of the decimal point. When num_digits is less or equal to zero, the ROUND function rounds on the left side of the decimal point. Use zero (0) for num_digits to round to the nearest integer....

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.

Note: this shortcut is unusual on a Mac because the Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys. 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.

The current selection on the worksheet is unaffected, so you can use this shortcut to quickly select the name box and define a new named range. Alt + F3 works in Windows only and it appears to have been introduced in Excel 2016. On the Mac there is no equivalent shortcut. However, you can use another shortcut, F6, to cycle through “panes”. It may take more than one click, but F6 will select the name box eventually....

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.

Note: you can use Control + mouse scroll wheel to zoom in and out on both Windows and Mac. On Windows, the shortcut appears to be new with Excel 2016, and changes increases the zoom level shown in the lower right of the worksheet by 15% each time Control + is used. Use Control - to zoom out by 15%. On the Mac, there is no native keyboard shortcut to zoom in Excel, as far as we know....

With these inputs, the TBILLYIELD function returns a yield of 2.51%, with percentage number format applied. 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. To enter valid dates directly inside a function, the DATE function is the best option. About treasury bills A treasury bill (also called a T-Bill) is a short-term debt obligation issued by the US Treasury Department....

Examples Numbers or punctuation characters inside a text string are unaffected: If a numeric value is given to UPPER, number formatting is removed. For example, if cell A1 contains the date June 26, 2021, date formatting will be lost and UPPER will return a date serial number as text: If necessary, you can use the TEXT function to workaround this limitation. Use TEXT to convert the number to a text value, then pass that value into UPPER:...