The QUARTILE function is automatic, and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, quartile returns the minimum value in the data.
The trick in this case is to arrange the...Read more
Use the QUARTILE function to get the quartile for a given set of data. QUARTILE 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 ...Read more
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...Read more
In this example, the goal is to calculate a count and percentage for each category shown in column B. For convenience, the category values in column B are in the named range category (B5:B122). To generate the count, we use the...Read more
In this example, we have a list of 100 issues in Columns B to D. Each issue has a date and priority. We are also using the named range dates for C5:C104 and priorities for D5:D105. Starting in column F, we have a summary table...Read more
This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right.
Working from the inside out, we use the SUBSTITUTE function to find all...Read more
MOD is nerdy, but cool. If you've ever struggled to create a more complex formula in Excel, you'll like this one. It shows you how to build a more complex formula step-by-step, without going crazy....Read more
In this example, the goal is to create a formula that will calculate the number of days between a start date in column B and an end date in column C that overlap a period defined by date 1 and date 2, which are variables that can be easily changed. You can use a formula like this to do things...Read more
The core of this formula is the MOD function. MOD takes a number and divisor, and returns the remainder after division, which makes it useful for formulas that need to do something every nth time.
In this case, the number is created with the COLUMN function, which return the column...Read more
The core of this formula is SUMIF, which is used to lookup the correct values for F and H. Using SUMIF to lookup values is a more advanced technique that works well when the values are numeric, and there are no duplicates in the "lookup table".
The trick in this case...Read more
