Purpose Return value Syntax =CEILING.PRECISE(number,[significance]) number - The number that should be rounded. significance - [optional] Multiple to use when rounding. Default is 1. Using the CEILING.PRECISE function The Excel CEILING.PRECISE function rounds a number up to a given multiple, where multiple is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned. The CEILING.PRECISE function takes two arguments , number and significance....

I’ve been playing around with the TEXTJOIN and CONCAT functions this week. These are both new functions in Excel 2016, introduced in the Office 365 subscription service. Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options. What’s nice about both of these functions is that they can handle cell ranges....

Explanation When conditional formatting is applied with a formula, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the active cell when the rule is created is assumed to be cell E5, with the range E5:E14 selected. 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:...

About This Shortcut This shortcut copies the formula in the cell above to the active (current) current cell and leaves the cell in edit mode. This is a convenient way to make an exact copy of a formula - no cell references are changed. A nice feature of this shortcut is that is leaves the cell in " edit mode " after the copy, so you can immediately edit. This can be handy when you want to copy a value from above and adjust the result manually....

Explanation In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the SUMPRODUCT function to get a total. The formula in G4 is: =SUMPRODUCT(COUNTIF(B5:B15,things)) The COUNTIF function counts the number of cells in a range that meet criteria. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers as the result, where each number represents the count of one thing in the criteria range....

Explanation In this example, the goal is to count test scores in column C that are less than 75. The simplest way to do this is with the COUNTIF function , which takes two arguments , range and criteria : =COUNTIF(range,criteria) The test scores in the range C5:C16 and we want to count scores less than 75 , so we configure COUNTIF like this: =COUNTIF(C5:C16,"<75") // returns 1 Since there is only one score in C5:C16 that is less than 75, COUNTIF returns 1 as a result....

Explanation In this example, the goal is to count the number of rows in the data that contain the value in cell G4, which is 19. The main challenge in this problem is that the value might appear in any column, and might appear more than once in the same row. If we wanted to simply count the total number of times a value appeared in a range, we could use the COUNTIF function ....

Explanation The core of this formula is the ROUNDUP function. The ROUNDUP function works like the ROUND function except that when rounding, the ROUNDUP function will always round the numbers 1-9 up. In this formula, we use that fact to repeat values. To supply a number to ROUNDUP, we are using this expression: (COLUMN()-2)/$B4 Without a reference, COLUMN generates the column number of the cell it appears in, in this case 3 for cell C4....

Transcript In this video we’ll look at how to apply data validation to an Excel Table . We’ll also look at how to use the values in a table for data validation. This table contains a list of projects showing a sales pipeline. The last column is meant to show the current state of a given opportunity. To the right, in column G, is a list of possible stages. To illustrate how data validation works with tables, I’ll set up the last column of the table to enforce the values in column G, by providing a dropdown list....

Purpose Return value Syntax =DEC2BIN(number,[places]) number - The decimal number you want to convert to binary. places - [optional] Pads the resulting binary number with zeros up to the specified number of digits. If omitted returns the least number of characters required to represent the number. Using the DEC2BIN function The input must be a valid number within the range [ -512, 511 ]. Errors Purpose Return value Syntax =DEC2HEX(number,[places]) number - The decimal number you want to convert to hexadecimal....

Purpose Return value Syntax =DOLLARDE(fractional_dollar,fraction) fractional_dollar - Dollar component in special fractional notation. fraction - The denominator in the fractional unit. 8 = 1/8, 16 = 1/16, 32 = 1/32, etc. Using the DOLLARDE function The DOLLARDE function is a financial function which converts values pricing entered with a particular fractional notation into an equivalent decimal number. It can be used for securities where pricing is given to the nearest 1/8, 1/16, 1/32, etc....

A unary operation is an operation with only one operand (input). The double unary (also called a double negative) is an operation used to coerce TRUE FALSE values to ones and zeros in more advanced formulas, especially formulas that work with arrays. For example, the screen above shows two groups that contain five values each. Three values match and two are different. The formula used to count matching values is:...

About This Shortcut Normally, when you click and drag worksheet tabs at the bottom of a workbook, you are reordering the tabs. Use this shortcut when you want to duplicate a worksheet. The cursor will change to a small plus (+) sign inside a document. When you release your mouse, a copy of the worksheet will be inserted at the location of the cursor. About This Shortcut Moves current selection one cell to the right of the active selection....

About This Shortcut This shortcut is an alternative to pressing enter/return when entering data. It’s useful when the next value you want to enter is above. About This Shortcut This shortcut is an alternative to pressing enter/return when entering data. It’s useful when the next value you want to enter is to the right.

Formulas are the heart of Excel. They can do everything from basic math to complex data analysis. But sometimes, they don’t work as expected. If you work in Excel a lot, you’ve probably seen many formula errors like #DIV/0, #NAME?, and #N/A. In fact, the more you work with formulas, the more errors you’ll run into :) Although formulas errors can be scary and frustrating, they are quite useful, because they tell you something is wrong....

About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell to the left. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell to the left. On a Mac, the command key (⌘) can be used instead of the control key. About This Shortcut If the active cell is empty, Excel will extend the selection to the first non-empty cell above the active cell....

Explanation In this example, the goal is to extract the nth word from the text string given in column B. The article below explains two approaches. The first approach is based on the new TEXTSPLIT function , which makes it very easy to split text with a custom delimiter. The second approach is a more complicated formula that works in older versions of Excel that do not provide the TEXTSPLIT function....

Explanation This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match. The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH function: Table1[#Headers] The nice thing about this reference is that it will automatically adjust to any changes in the table....

Explanation The goal is to retrieve the nth matching record in a table when targeting a specific product. For example, if the value in cell H4 is “A”, the formula in H7 should return the name “John”, since this is the first name in the table associated with product “A”. In the same way, the formula in H8 should return ‘Juan’, since this is the second name associated with product “A”....

Purpose Return value Syntax =GETPIVOTDATA(data_field,pivot_table,[field1, item1],...) data_field - The name of the value field to query. pivot_table - A reference to any cell in the pivot table to query. field1, item1 - [optional] A field/item pair. Using the GETPIVOTDATA function Use the GETPIVOTDATA function to query an existing Pivot Table and retrieve specific data based on the pivot table structure. The advantage of GETPIVOTDATA over a simple cell reference is that it collects data based on structure , not cell location....