Posts

where “groups” is the named range C5:C14, and “scores” is the named range D5:D14. The result is a rank for each person in their own group. Note: although data is sorted by group in the screenshot, the formula will work fine with unsorted data. The COUNTIFS function can perform a conditional count using two or more criteria. Criteria are entered in range/criteria pairs. In this case, the first criteria restricts the count to the same group, using the named range “groups” (C5:C14): By itself, this will return total group members in group “A”, which is 5....

Here we have a table with 3 rows of data for each client, for the months April, May, and June. Let’s use conditional formatting to highlight these rows to match the data. We can do this with a formula that groups rows by three’s. As usual, I’ll set up dummy formulas to figure out a working formula. Then I’ll create a conditional formatting rule in the final step. To start off, I want to get a row number for each row, starting with 1....

where “data” is the named range B5:B13. Note: this is a multi-cell array formula, entered with control + shift + enter. The example shown uses several formulas, which are described below. At a high level, the MMULT function is used to compute a numeric rank in a helper column (column C), and this rank is then used by an INDEX and MATCH formula in column G to extract unique values....

where times (C5:C15), and states (D5:D15) are named ranges. To sum time conditionally by each state, we are using the SUMIFS function in G5: The reference to F5 is relative. When the formula is copied down the column, F5 changes at each new row. The two named ranges, times and states, are are fixed and do not change. In each row, SUMIFS correctly shows the total hours logged for a given state....

As the formula is copied down, the data is separated into three fields: Name, Street, and City/State/Zip. Note: In the generic version formula, n represents the number of cells between records, and k represents the number of fields to extract. As this formula is copied down, it extracts the values for each record into cells in a single row. Note: the formula explained below builds on more basic examples explained here....

In the first expression, the value is compared to the smaller of the two numbers, determined by the MIN function. In the second expression, the value is compared to the larger of the two numbers, determined by the MAX function. The AND function will return TRUE only when the value is greater than the smaller number and less than the larger number. Include boundaries To include the boundary numbers (num1 and num2), just adjust the logic like so: Now the AND function will return TRUE only when the value is greater than or equal to the smaller number and less than or equal to the larger number....

What is Excel? Excel is a spreadsheet program with a full spectrum of capabilities. Using Excel, you can run calculations, make lists and charts, analyze and organize information, track financial data, and much more. Let’s take a look. Every Excel workbook is a collection of worksheets. All the work you do in Excel will be done in a worksheet. Worksheets appear as tabs at the bottom of the Excel window, and the workbook name appears in the window title at the top....

Here are 12 great keyboard shortcuts to quickly format your data.

In this tutorial, you will learn all about the Excel Paste Special feature and useful Excel Paste Special Shortcuts. What is Excel Paste Special? What happens when you copy a cell in excel and paste it somewhere else? It copies not only the content of the cell but also the format such as background color, font color, borders(as shown below). But what if you want to copy only the cell contents and not the formatting, or only the formatting and not the values, or only the formula within it?...

An issue that comes up quite often in Excel is numbers that have been entered or formatted as text values. This can cause many headaches when trying to troubleshoot why a formula like a SUM is not giving the correct answer. Even though the data looks like a number, it can actually be a text value and will be ignored from any numerical calculations like a sum. In this post, I will show you how you can identify such numbers which are stored as text values, and 7 ways you can use to convert the text into a proper number value....

In the example shown, the formula in D5 is: When you have a valid date in Excel, you and just add days directly. Day values can be positive or negative. For example, with a date in A1, you can add 100 days like so: In the example shown, the formula is solved like this: When formatted as a date, 36868 is December 8, 2000. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

For example, you can add two different time values or date values or you can calculate the time difference between two given dates/times. In this tutorial, I will show you a couple of ways to perform calculations using time in Excel (such as calculating the time difference, adding or subtracting time, showing time in different formats, and doing a sum of time values) How Excel Handles Date and Time? As I mentioned, dates and times are stored as numbers in a cell in Excel....

In the example shown, the top table is set up to convert from inches to centimeters, and the bottom table is set up to convert from centimeters to inches. The formulas in F5 and F9, which are copied across and down, are as follows: 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....

The result is the column reference as one or more letters. ADDRESS function Working from the inside out, the first step is to construct an address that contains the correct column reference. We can do this with the ADDRESS function, which will return the address for a cell based on a given row and column number. For example: By providing 4 for the optional abs_num argument, we can get a relative reference: Note the result from ADDRESS is always a text string....

Because there are three empty cells in the range C5:C16, COUNTBLANK returns 3. COUNTBLANK function The simplest way to count empty cells in a range is to use the COUNTBLANK function. In the example shown, the formula in F6 is: Because there are three empty cells in the range C5:C16 , COUNTBLANK returns 3. COUNTBLANK is fully automatic, so there is nothing to configure. COUNTIFS function You can also use the COUNTIFS function to count empty cells by passing in an empty string ("") as criteria like this: COUNTIF returns the same result as COUNTBLANK: 3....

where data is the named range B5:B15 and the numbers in column D are entered as text values. COUNTIF function The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?) for partial matching. You would think you could use the COUNTIF function with the asterisk wildcard () like this: The problem however is using any wildcard character means criteria must be handled as a text value in double quotes ("") whereas the values in column B are TRUE numbers....

There’s an Index Column button in the Add Column tab of the power query editor. This will add a column containing a sequentially increasing integer. There are also options for which number to start the sequence with and how much to increment each row. If we add an index column starting at 1 and increment by 1 for each row, then this can be used as a serial number in our data....

Note: The point of INDIRECT here is to build a formula where the sheet name is a dynamic variable. For example, you could change a sheet name (perhaps with a drop down menu) and pull in information from different worksheet. In this example, we have Sheet names in column B, so we join the sheet name to the cell reference A1 using concatenation: After concatenation, we have: INDIRECT recognizes this as a valid reference to cell A1 in Sheet1, and returns the value in A1, 100....

The CEILING.PRECISE function takes two arguments, number and significance. The number argument is the numeric value to round up, and is the only required argument. With no other input, CEILING.PRECISE will round number up to the next integer. The significance argument is the multiple to which number should be rounded. In most cases, significance is provided as a numeric value, but CEILING.PRECISE can also understand time entered as text like “0:15”....

Basic Example The FIND function is designed to look inside a text string for a specific substring. When FIND locates the substring, it returns a position of the substring in the text as a number. If the substring is not found, FIND returns a #VALUE error. For example: Note that text values entered directly into FIND must be enclosed in double-quotes (""). Case-sensitive The FIND function always case-sensitive: TRUE or FALSE result To force a TRUE or FALSE result, nest the FIND function inside the ISNUMBER function....