Posts

Purpose Return value Syntax =MAXA(value1,[value2],...) value1 - Number, reference to numeric value, or range that contains numeric values. value2 - [optional] Number, reference to numeric value, or range that contains numeric values. Using the MAXA function The MAXA function returns the largest numeric value in a range of values. Like the MAX function , MAXA ignores empty cells. However, unlike the MAX function, MAXA evaluates the logical values TRUE and FALSE as 1 and 0, and evaluates text as zero when these values appear in a range or cell reference....

About This Shortcut This shortcut minimizes the current workbook window. About This Shortcut Maximize or restores the selected workbook window. This shortcut acts as a toggle.

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

Explanation In the example shown, we want to test if all values in each row are equal. To do this, we use an expression that compares the value in the first column (B5) to the rest of the columns (C5:F5): B5=C5:F5 Because we are comparing one cell value to values in four other cells, the result is an array with four TRUE or FALSE values. In row 5, all values are equal, so all values are TRUE:...

Purpose Return value Syntax =MUNIT(dimension) dimension - An integer for the size of the unit matrix. Using the MUNIT function The MUNIT function returns a unit matrix for a given dimension, n , with a size of n x n . The unit matrix is also called the identity matrix. The MUNIT function takes just one argument, dimension , which should be a positive integer. The resulting matrix contains ones on the main diagonal and zeros in every other position....

Purpose Return value Syntax =ODDFYIELD(sd,md,id,fd,rate,pr,redem,freq,[basis]) sd - Settlement date of the security. md - Maturity date of the security. id - Issue date of the security. fd - First coupon date. rate - Annual coupon rate of security. pr - Price of security. redem - Redemption value per $100 face value. freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). basis - [optional] Day count basis (see below, default =0)....

Explanation In this example, the goal is to calculate the percentage sold for each item listed in the table, where original number (Total) is in column C and the Sold number is in column D. In other words, if we know we started with 144 apples, and sold 108 apples, we want to calculate that 75% of the apples were sold. The general formula for this calculation, where “x” is the percentage sold, is:...

This is an example of a 100% stacked area chart. This tricky chart type can be quite hard to read. The idea is to visually show a percentage distribution across categories at specific intervals, but you lose the ability to compare absolute numbers, unless you add as data labels. In this case, data labels are only used to display the series name. So, what can you see with this chart? Primarily, you can see that sales of Retrobags are collapsing while Organic Flannel makes up the slack....

Explanation The MROUND function rounds a number to the nearest given multiple. The multiple to use for rounding is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned. You can use MROUND to round prices, times, instrument readings or any other numeric value. In the example shown, we are using MROUND to round the price in column B using the multiple in column C....

About This Shortcut This shortcut selects the data area of a table, when the cursor is in an Excel table. Behavior changes as the shortcut is used more than once: First time: selects table data Second time: selects table data + table header Third time: selects entire worksheet About This Shortcut This shortcut clears or resets the filter for a selected slicer. You must select the slicer first before using the shortcut....

Explanation In this example, the goal is to subtotal invoices by age, where age represents the number of days since the invoice was issued. This problem can be solved with the SUMIFS function and the COUNTIFS function, as explained below. For convenience, age (E5:E16) and amount (D5:D16) are named ranges . SUMIFS function The SUMIFS function is designed to sum cells that meet multiple criteria. SUMIFS takes at least three arguments like this:...

Explanation In this example, we have comma-separated text in column B. The goal is to split the text in column B into columns D through G while at the same time converting the numbers to true numeric values. The challenge is that TEXTSPLIT always returns text, so we need a way to convert the numbers while leaving the text values alone. The problem with TEXTSPLIT To split the text in column B into separate columns, we can use the TEXTSPLIT function with a simple formula like this:...

Note: the discussion on this page is about converting TRUE and FALSE values to 1s and 0s. However, the techniques discussed on this page are more general and will also work to convert any number that is text to a numeric value. In more advanced Excel formulas, you might run into the double negative operation (–): What the heck is that, and what is it doing? The double negative (sometimes called the even more nerdy “double unary”) coerces TRUE or FALSE values to their numeric equivalents, 1 and 0....

Purpose Return value Syntax =TIMEVALUE(time_text) time_text - A date and/or time in a text format recognized by Excel. Using the TIMEVALUE function Sometimes, times in Excel appear as text values that are not recognized properly as time. The TIMEVALUE function is meant to parse a time that appears as a text value into a valid Excel time. A native Excel time is more useful than text because it is a numeric value that can be formatted as time and directly manipulated in a formula....

Explanation The SUMIFS function is designed to sum numeric values using multiple criteria. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is: =SUMIFS(value,name,$G5,stage,H$4) The first part of the formula sums opportunities by salesperson: =SUMIFS(value,name,$G5 // sum by name Sum range is the named range values Criteria range 1 is the named range name Criteria 1 comes from cell G5 Notice $G5 is a mixed reference , with the column locked and the row relative....

Purpose Return value Syntax =UNICHAR(number) number - Code point for a Unicode character in decimal. Using the UNICHAR function The UNICHAR function returns the Unicode character at a given code point, provided as a number in decimal format . To illustrate, the Euro symbol (€) has a code point of 8364 in decimal notation. The following formula will return the Euro character: =UNICHAR(8364) // returns euro sign "€" Unicode numbers Unicode assigns each character a unique numeric value and name....

Explanation This formula is a good example of nesting one function inside another. At the core, this formula uses the IF function to return a check mark (✓) when a logical test returns TRUE: =IF(logical_test,"✓","") If the test returns FALSE, the formula returns an empty string (""). For the logical test, we are using the COUNTIF function like this: =COUNTIF(list,B5) COUNTIF returns a count of how many times the value in B5 occurs in the named range list (E5:E9)....

Explanation In this example, we have a table of employee locations like this on Sheet2: On Sheet1, we retrieve the building location for each team member using this formula: =VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0) The lookup value is the employee ID, from cell B5. For the table array, we use the range $B$5:$C$104 qualified with a sheet name, and locked as an absolute reference , so that the range does not change as the formula is copied down:...

Explanation In this example, the goal is to calculate a weighted average of scores for each name in the table using the weights that appear in the named range weights (I5:K5) and the scores in columns C through E. A weighted average (also called a weighted mean ) is an average where some values are more important than others. In other words, some values have more “weight”. We can calculate a weighted average by multiplying the values to average by their corresponding weights, then dividing the sum of results by the sum of weights....

Introduction For decades, INDEX and MATCH have been the go-to solution for handling complex lookup problems. Unlike VLOOKUP , INDEX and MATCH are based on numeric positions: the MATCH function locates the position of a value, and the INDEX function retrieves a value at that position. This approach makes INDEX and MATCH highly versatile, at the cost of more configuration. However, with the introduction of XLOOKUP in 2019, Excel users have a powerful new lookup option available....