where data is the Excel Table in B5:E16. A similar formula in G12 returns the 3 biggest losers. These results are dynamic and will immediately update if data changes. Read below for a detailed explanation of both formulas. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021. If you are new to FILTER, see this video: Basic FILTER function example Top 3 gainers In the example shown, the formula to return the top 3 gainers in cell G5 is: Working from the inside out, the first task is to extract the three rows from the data that have the largest change values....

The results in column E are decimal values with the percentage number format applied. The same formula can be used to calculate things like variance between this year and last year, variance between a budget and actual values, and so on. The concept of variance requires a baseline value and a “new” value. The baseline value is subtracted from the new value and the result is divided by the baseline value....

which ensures the result will never be greater than $1000. Inside MIN, the value in C6 is multiplied by10%, and the result appears at the first number given to MIN. The number 1000 is supplied as the second value. The MIN function simply returns the smaller of the two values: When C610% is < 1000, the result is C610% When C610% is > 1000, the result is 1000 When C610% is = 1000, the result is 1000...

which returns 39, the age of “JILL SMITH”. Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. Working from the inside-out, EXACT is configured to compare the value in E5 against names in the range B5:B14: The EXACT function performs a case-sensitive comparison and returns TRUE or FALSE as a result. Only values that match exactly will return TRUE....

Whenever you select a chart in later versions of Excel, you’ll see three icons appear at the upper right edge: the plus icon, the paint brush icon, and the filter icon. The plus icon is actually a fly out menu called Chart Elements. When you click the icon, you’ll see a list of common chart elements displayed as checkboxes. This menu lets you quickly enable and disable chart elements. Here you’ll find options for a long list of chart elements, including axis and axis titles, the chart title, Data labels, Data table, error bars, gridlines, legend, and trendlines....

Note: you can see the same data in a clustered bar chart. How to make this chart 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.

To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm. Since there are 24 hours in a day, 60 minutes in each hour, and 60 seconds in each minute, you need to divide by 24 * 60 * 60 = 86400 in order to convert decimal seconds to a value that Excel will recognize as time. After dividing by 86400, you can apply a time format of your choice, or use the result in a math operation with other dates or times....

which returns a value of 60, since there are 60 seconds in 1 minute. Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24, convert to decimal minutes by multiplying the value by 1440 (24 * 60) , and convert to seconds by multiplying by 86400 (24 * 60 * 60). With the time value 6:00 cell A1, you can visualize the conversion like this: The Excel time 6:00 converts to 21,600 seconds....

where things is the named range E5:E7. Note: COUNTIF is not case-sensitive. 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. In this case, the named range things (D5:D7) contains 3 values, so COUNTIF returns 3 results in an array as shown below: Since “apple” appears twice, “pears” appears three times, and “kiwis” appears once, the array contains the numbers 2, 3, and 1....

In the example shown, the data validation applied to B5:B11 is: This formula uses the SEARCH function to test user input for each value in the named range “list”. The search logic is “contains” — when a value from “list” is found is found, SEARCH returns the position of the value as a number. If not found, SEARCH returns an error. The ISNUMBER function then converts numbers to TRUE and errors to FALSE, and the double negative operator changes the TRUE FALSE values to 1s and zeros....

Doughnut charts should be avoided when there are many categories, or when categories do not sum to 100%. Pros Simple, compact presentation Can be read “at a glance” Excel will calculate percentages automatically Cons Difficult to compare relative size of slices Become cluttered and dense as categories are added Limited to part-to-whole data Poor at showing change over time Tips Limit categories Consider other charts to show change over time...

Example To use COMBIN, specify the total number of items and “number chosen”, which represents the number of items in each combination. For example, to calculate the number of 3-number combinations, you can use a formula like this: The number argument is 10 since there are ten numbers between 0 and 9, and number_chosen is 3, since there are three numbers chosen in each combination. This result can be seen in cell D8 in the example shown....

Compatibility functions can still be used in later versions of Excel to ensure backward compatibility. However, Microsoft recommends that users deploy the newer functions (compatibility function replacements) when backward compatibility isn’t required. Not all new functions with similar names indicate compatibility functions. For example, the NETWORKDAYS function, which calculates workdays between dates, is not listed as a compatibility function even though NETWORKDAYS.INTL is a newer function with the same purpose....

In the screen above, the formulas used for concatenation are: Note that in the last example, we are using the TEXT function to format the date in B8 as a date in “mmm d” format. Concatenation with functions Excel contains three functions you can also use for concatenation: the CONCATENATE function, the CONCAT function, and the TEXTJOIN function. CONCAT and TEXTJOIN are new functions available in Office 365 and Excel 2019....

The settlement date is the date the investor takes possession of a security. The maturity date is the date when the investment ends and the principle plus accrued interest is returned to the investor. The frequency is the number of interest payments per year. Basis specifies the method used to count days (see below). In the example shown, the formula in F6 is: COUPDAYBS returns an integer, so use a number format and not a date format to display properly....

Syntax The generic syntax for the IF function looks like this: The first argument, logical_test, is an expression that should return either TRUE or FALSE. The second argument, value_if_true, is the value to return when logical_test is TRUE. The last argument, value_if_false, is the value to return when logical_test is FALSE. Both value_if_true and value_if_false are optional, but at least one of them must be provided. For example, if cell A1 contains 80, then:...

Examples The ISODD function returns TRUE or FALSE: If cell A1 contains 11, the formula below returns TRUE: Only the integer portion of value is tested. If value is a decimal number, the decimal portion is truncated: Notes If value is not numeric, ISODD will return the #VALUE error. Only the integer portion of value is tested; decimal values are truncated. Use the ISEVEN function to test for even numbers....

Dynamic array formulas are a huge change to the Excel formula engine because they let you easily work with multiple values at the same time in a formula. In practical terms, this means array formulas “just work”. In Legacy Excel, most array formulas still need to be entered with control + shift + enter. Example The LEN function returns the length of a text string as a number: To get a total of all characters in a range, you can wrap LEN inside the SUM function like this: This is a simple array formula, but it must be entered with control + shift + enter in Legacy Excel: Note: Excel will add the curly brackets automatically when the formula is entered with control + shift + enter....

The N function is provided for compatibility with other spreadsheet programs. In most cases, using the N function is unnecessary, because Excel automatically converts values when needed. However, the N function is a simple way to convert TRUE and FALSE to their numeric equivalents, 1 and 0, as mentioned below. Examples The N function converts text values to zero: Numeric values and errors are not affected: The N function converts TRUE to 1 and FALSE to zero: There are several ways to perform this conversion in Excel, which is useful in more advanced formulas....

To use PERCENTILE.INC, provide a range of values and a number between 0 and 1 for the “k” argument, which represents percent. For example: You can also specify k as a percent using the % character: PERCENTILE.INC returns a value greater than or equal to the specified percentile. In the example shown, the formula in G5 is: where “scores” is the named range C5:C14. PERCENTILE.INC vs. PERCENTILE.EXC PERCENTILE.INC includes the full range of 0 to 1 as valid k values, compared to PERCENTILE....