Purpose Return value Syntax =REPT(text,number_times) text - The text to repeat. number_times - The number of times to repeat text. Using the REPT function The REPT function repeats characters a specified number of times. Although REPT can repeat numbers as well as text, the result from REPT is always a text value . REPT takes two arguments, text and number_times . Text is the character(s) to repeat, and number_times is the number of times text should be repeated....
Explanation In the example, cell C6 contains this formula: =MROUND(B6,5) The value in B6 is 17 and the result is 15 since 15 is the nearest multiple of 5 to 17. Other multiples As you’d expect, you can use MROUND to round to other multiples as well: =MROUND(number,10) // nearest multiple of 10 =MROUND(number,50) // nearest multiple of 50 =MROUND(number,.05) // nearest 5 cents And so on. Force up or down You can use the CEILING function to force rounding up to the nearest multiple and the FLOOR function to force rounding down to the nearest multiple....
About This Shortcut This shortcut will select the entire worksheet. The behavior changes on subsequent use If the cursor is inside a region with adjacent, non-empty cells: First time - will select the region that contains non-empty cells Second time - will select the entire worksheet About This Shortcut Hold down the shift key and click elsewhere on the worksheet to extend the current selection to include adjacent cells.
Transcript In this video, we’ll look at a more complicated example of a shortcut recipe to remove blank rows. In this case, some rows are partially blank, so we need to add a formula in a helper column to test and make sure that the entire row is blank before deleting. This worksheet contains measurement data. All together, the table has more than 1000 rows, and each row has 15 measurements, using codes A through F....
Explanation In this example, the goal is to construct a formula that will subtotal the amounts in column D by client and month as seen in the range G5:I8. A big part of the problem is to set up the proper references so that the formula can be entered once, and copied throughout G5:I8. The solution explained below is based on the SUMIFS function. Summary table setup The first step in solving this problem is creating the summary table seen in the range F4:I8....
Explanation In this example, the goal is to sum the amounts in the table using the “Start” and “End” values in columns F and G. For convenience, all data is in an Excel Table called data , which means we can use the structured reference data[Amount] to refer to amounts. An easy way to solve this problem is to use the SUMIFS function, as explained below. SUMIFS function The SUMIFS function can sum values in ranges based on multiple criteria....
About This Shortcut This shortcut toggles italics on and off for the current selection. On Windows, Ctrl 3 also toggles italics. About This Shortcut This shortcut toggles underlining on and off for the current selection. On Windows, Ctrl 4 also toggles underlining.
Explanation In this example, the goal is to extract a list of unique rows from the range B5:C15. The easiest way to do this is to use the UNIQUE function . By default, UNIQUE will extract unique values in rows, so there is no special configuration needed. Working from the inside out, we give UNIQUE the full range of data, including the Group and Color columns: UNIQUE(B5:C15) The UNIQUE function evaluates all 11 rows and returns the 6 unique rows in an unsorted two-dimensional array that looks like this:...
Explanation In this example, the goal is to calculate the correct commission for both Agent and Broker based on a 3% commission which is split according to the table in G7:I11, which is named split . Notice the amount going to the Agent and Broker changes as the total amount increases, which the Agent getting a larger share for higher amounts. The example assumes all commissions are 3%, as set in cell H4, the named range rate ....
Purpose Return value Syntax =AMORDEGRC(cost,purchase,first,salvage,period,rate,[basis]) cost - Asset cost. purchase - Asset purchase date. first - First period end date. salvage - Asset salvage value. period - Period for which to calculate depreciation. rate - Rate of depreciation. basis - [optional] Day count basis (default =0). Using the AMORDEGRC function The Excel AMORDEGRC function returns the depreciation for each accounting period by using a depreciation coefficient. This function is provided for the French accounting system....
Purpose Return value Syntax =AMORLINC(cost,purchase,first,salvage,period,rate,[basis]) cost - Asset cost. purchase - Asset purchase date. first - End date of first period. salvage - Asset salvage value. period - The period for which to calculate depreciation. rate - Rate of depreciation. basis - [optional] Day count basis (see below, default =0). Using the AMORLINC function The Excel AMORLINC function returns the depreciation for a given accounting period. This function is provided for the French accounting system....
Explanation In order to understand this problem, make sure you understand how percentage number formatting works . In a nutshell, percentages are decimal values: 0.1 is 10%, 0.2 is 20%, and so on. The number 1, when formatted as a percentage, is 100%. More on number formats here . The goal of this example is to limit incoming percentage values so that they fall within an upper and lower threshold. Negative values and values over 100% are not allowed, so the final result must be a number between zero and 1 (0-100%), inclusive....
Purpose Return value Syntax =CODE(text) text - The text for which you want a numeric code. Using the CODE function The CODE function returns a numeric code for a given character. For example, CODE(“a”) returns the code 97: =CODE("a") // returns 97 With the character “a” in cell A1, the formula below returns the same result: =CODE(A1) // returns 97 The CODE function takes a single argument , text , which is normally a text value....
Introduction The complex number system is an extension of real numbers. Historically, the invention of complex numbers allowed mathematicians to find the roots of previously unsolvable polynomials. Nowadays, engineers use complex numbers to solve problems related to electronics, signal processing, and fluid dynamics. Famous equations like Euler’s Identity, Maxwell’s equations, and Schrödinger’s equation in quantum mechanics are examples of where the number system is used. Excel supports complex numbers with functions that allow users to perform addition, multiplication, exponentiation, and other operations....
Explanation The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows: The year value is extracted with the RIGHT function: RIGHT(B5,2)+2000 RIGHT gets the right-most 2 characters from the original value. The number 2000 is added to the result to create a valid year....
About This Shortcut This shortcut copies the value in the cell above to the selected cell and leaves the cell in edit mode. If the cell above is a formula, only the value is copied. About This Shortcut This shortcut adds a hyperlink to the currently selected cell. When the shortcut is performed, Excel will display the Add Hyperlink dialog box shown below: Note: the HYPERLINK function can be used to create a link with a formula....
Explanation First, the LEN function counts total characters in the cell B5. Next SUBSTITUTE removes all “line returns” from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters without carriage returns. The second count is subtracted from the first, and 1 is added to the final result, since the number of lines is the number of returns + 1....
Explanation The length of a line can be calculated with the distance formula, which looks like this: Distance is the square root of the change in x squared plus the change in y squared, where two points are given in the form (x 1 , y 1 ) and (x 2 , y 2 ). The distance formula is an example of the Pythagorean Theorem applied, where the change in x and the change in y correspond to the two sides of a right triangle, and the hypotenuse is the distance being computed....
There are eight functions in Excel that work differently than you might realize. I call these “range-based conditional functions” or RACON (Ray-con) functions for short, because these functions apply criteria to a range and return a single result based on supplied criteria. In other words, these functions perform range-based , conditional aggregation. Here is the list of RACON functions: Function Purpose AVERAGEIF Conditional average with one condition AVERAGEIFS Conditional average with one or more conditions COUNTIF Conditional count with one condition COUNTIFS Conditional count with one or more conditions MINIFS Conditional minimum with one or more conditions MAXIFS Conditional maximum with one or more conditions SUMIF Conditional sum with one condition SUMIFS Conditional sum with one or more conditions Click any function above for details and many examples....
Explanation In this example, the goal is to get the day name (i.e. Monday, Tuesday, Wednesday, etc.) from a given date. There are several ways to go about this in Excel, depending on your needs. This article explains three approaches: Display date with a custom number format Convert date to day name with TEXT function Convert date to day name with CHOOSE function For all examples, keep in mind that Excel dates are large serial numbers , displayed as dates with number formatting....
