Purpose Return value Syntax =QUARTILE.INC(array,quart) array - A reference containing data to analyze. quart - The quartile value to return. Using the QUARTILE.INC function Use the QUARTILE.INC function to get the quartile for a given set of data. QUARTILE.INC takes two arguments, the array containing numeric data to analyze, and quart, indicating which quartile value to return. The QUARTILE function accepts 5 values for the quart argument, as shown in the table below....
A scatter plot is a built-in chart type in Excel meant to show the relationship between two variables. A scatter plot works by placing one variable on the vertical axis and a different variable on the horizontal axis. Each piece of data is then plotted as a discrete point on the chart. In a scatter plot, both the X and Y axis display values – an XY chart has no category axis....
Explanation In this example, the goal is to sum numbers in the range C5:C16 when text in the range B5:B16 contains the substring “red” OR the substring “blue”. In other words, if the text in B5:B16 contains either of these two text values in any location, the corresponding number in C5:C16 should be included in the sum. We can’t use the SUMIFS function with two criteria because SUMIFS is based on AND logic, and both criteria would need to be TRUE....
Explanation At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a string. To parse the input string into an array or letters, we use MID, ROW, LEN and INDIRECT functions like this: MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1) LEN returns the length of the input text, which is concatenated to “1:” and handed off to INDIRECT as text....
Purpose Return value Syntax =VALUE(text) text - The text value to convert to a number. Using the VALUE function The VALUE function is meant to convert a text value that represents a number into a numeric value. The text can be a date, a time, or any other number, so long as the format can be recognized by Excel. When the conversion is successful, the result is a numeric value with no number formatting....
Explanation This is a standard VLOOKUP formula. It requires a table with lookup values (in this case, dates) to the left of the values being retrieved. The lookup value comes from cell E6, which must be a valid date. The table array is the range B6:C11, and the column index is 2, since the amounts are in the second column of the table. Finally, zero is provided for the final argument to force an exact match....
Transcript In this video, I’ll explain the basic idea of dynamic array formulas. Dynamic Array formulas are the biggest change to the Excel formula engine in years. Maybe the biggest change ever. This is because Dynamic Arrays make it easy to work with many values at the same time. For many users, this will be the first time they understand and use array formulas. Let’s look at a basic example....
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. About This Shortcut This shortcut allows you to display various lists within Excel without using a mouse. With Alt + down arrow, you can access lists in 3 different contexts:...
Explanation In this example, the goal is to create a formula that calculates an average salary by department, where data is an Excel Table in the range B5:D16. The solution shown requires three general steps: Create an Excel Table called data List unique departments with the UNIQUE function Calculate averages with the AVERAGEIFS function Create the Excel Table One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed....
Explanation Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. However, by using your own formula, you have more flexibility and control. In this example, a conditional formatting rule is set up to highlight cells in the range C5:G15 when then are greater than the value entered in cell J6. The formula used to create the rule is:...
Explanation In this example, the goal is to count the number of cells in column D that contain dates that are between two variable dates in G4 and G5. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. For convenience, the worksheet contains two named ranges : date (D5:D16) and amount (C5:C16). The named range amount is not used to count dates, but can be used to sum amounts between the same dates, as seen below....
Explanation In this example, the goal is to count ages in column C according to the brackets defined in columns E and F. All data is in an Excel Table named data defined in the range B5:C16. A simple way to solve this problem is with the COUNTIFS function. If you are using Excel 365 or Excel 2021, another easy way to solve this problem is with the FREQUENCY function. Both approaches are explained below....
Explanation The TODAY function takes no arguments; it is entered with empty parentheses. When you enter the TODAY function in a cell, it will display the current date. Each time the worksheet is recalculated or opened, the date will be updated. The TODAY function only inserts the date, time is not included. If you need to insert the current date in a way that will not change, use the keyboard shortcut Ctrl + ;...
An Excel Table is a special object for managing data in Excel. Excel Tables have a name which appears in the name box when the entire table is selected. To quickly create an Excel Table, select any cell in the data, and use the keyboard shortcut control + T. Links to video demos appear below this article. For a complete introduction to Excel Tables see: Excel Tables . Key advantages to Excel Tables Compared to manually formatted tables, Excel Tables have several key advantages:...
Explanation At the core, this formula is composed of two sets of the COUNTIF function wrapped in the IF function . The outer IF + COUNTIF first checks to see if the value in question (B4) appears more than once in the list: =IF(COUNTIF($B$4:$B$11,B4)>1 If not, the outer IF function returns an empty string ("") as a final result. If the value does appear more than once, we run another IF + COUNTIF combo....
Explanation In this example, the goal is to generate a list of quarter start and quarter end dates. This can be done by combining the SEQUENCE function with the EDATE and EOMONTH functions, as explained below. The SEQUENCE function The SEQUENCE function generates a list of sequential numbers in an array. For example, the formula below generates a sequence of 5 numbers that begin with 1 and end with 5:...
Explanation The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. Dates are just serial numbers in Excel, so earlier dates are always less than later dates. In the above formula, any dates that are greater than or equal to the start date AND less than or equal to the end date will pass both tests and the AND function will return TRUE, triggering the rule....
Explanation In this example, the goal is to calculate exactly how much of a task, shift, or event falls inside one or more defined blocks of time. The formula accepts a start and end time for the overall task or shift, as well as a start and end time for the block of interest. In the worksheet shown, the Start times are entered in column B, and the End times are in column C....
Transcript In this video, we’ll look at how to add one or more slicers to an Excel table. All Excel Tables come with a powerful filter enabled by default, which makes it easy to filter by one or more fields. But you can also add a slicer to a table. Slicers provide the same function as filters. They let you selectively display rows based on field values. However, instead of drop-down menus, slicers provide large, friendly buttons that are always visible....
Transcript In a previous video, we built a 100% stacked column chart, and added data labels to show actual amounts in an abbreviated custom number format. The result is a chart that shows a proportional breakdown of each quarter by region. Looking at the chart, you might wonder how to show the actual percentages in each bar? This isn’t hard to do, but it does take a little prep work....
