Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes
Here are three pictures to show how a time of day can be rounded…
• To its nearest 5-minute mark.
• Up, to its next 5-minute mark.
• Down, to its last 5-minute mark.
…
Blog Archives
Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes
Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour
Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour
Here are three pictures to show how a time of day can be rounded…
• To its nearest quarter-hour.
• Up, to its next quarter-hour.
• Down, to its last quarter-hour.
…
Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour
Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour
Here are three pictures to show how a time of day can be rounded…
• To its nearest half-hour.
• Up, to its next half-hour.
• Down, to its last half-hour.
…
Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour
Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour
Here are 3 formulas that round an elapsed time by the hour, in terms of nearest hour, up to the next hour, and down to the last (lower) hour.
In the picture, notice the differences for each employee’s elapsed time in decimalized hours,
…
Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range
Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range
When you want to sum or average a range of cells that need to each be rounded beforehand, here’s an efficient alternative to rounding each cell individally. In the pictured example, daily rainfall amounts with one decimal are to be summed,
…
Tom’s Tutorials For Excel: Separating Date and Time From NOW.
Tom’s Tutorials For Excel: Separating Date and Time From NOW.
Here’s how you can store the date in one cell, and the time in another cell, of a source cell that contains both a date and time, such as if that source cell were holding the NOW function.
…
Tom’s Tutorials For Excel: Parsing an Email Address for Recipient and Domain Names
Tom’s Tutorials For Excel: Parsing an Email Address for Recipient and Domain Names
If you have a list of email addresses and you want to parse the recipient and domain names, you can do so with the following formulas as shown in the picture. Note that the formulas will take into account the varying lengths of recipient and domain names.
…
Tom’s Tutorials For Excel: Ranking a List in Random Order
Tom’s Tutorials For Excel: Ranking a List in Random Order
Here’s an example of setting up your worksheet to randomly rank a list of items. In this first set of pictures, the list of names is randomly ranked three times, simply by hitting the F2 key on any cell in the worksheet,
…
Tom’s Tutorials For Excel: Sorting a List in Random Order
Tom’s Tutorials For Excel: Sorting a List in Random Order
Here’s how you can sort a table in a random order, instead of strictly as ascending or descending.
In the example, a company has 10 coveted parking spaces that are nearer to the office building.
…
Tom’s Tutorials For Excel: Using the RAND Function to Extract Random Data
Tom’s Tutorials For Excel: Using the RAND Function to Extract Random Data
Here are examples of how numbers or data can be extracted using the RAND function.
The examples and formulas listed below relate to the following picture.
Example 1: Randomly return a number between numbers,
…
Tom’s Tutorials For Excel: SUMIF With Character Count Criteria
Tom’s Tutorials For Excel: SUMIF With Character Count Criteria
With the question mark wildcard character, which represents one placeholder character of any kind, you can sum or count items in a list based on a minimum, maximum, or exact count of characters.
In the pictured example,
…
Tom’s Tutorials For Excel: Calculating Times Between Time Zones
Tom’s Tutorials For Excel: Calculating Times Between Time Zones
With the TIME function you can add or subtract times between time zones.
Then you can adjust the formula cells for a recognizable time format.
The formula in cell D3 copied down to cell D9 is
=IF(C3=0,B3-TIME(C3,,),B3+TIME(ABS(C3),,))
Select the range,
…
Tom’s Tutorials For Excel: Counting All Data Types in a Range
Tom’s Tutorials For Excel: Counting All Data Types in a Range
Any data that you put in a cell will always fall into one of four types:
• Text
• Numeric
• Boolean (TRUE or FALSE)
• Error
In the picture,
…
Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String
Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String
Here are formulas to strip away the last Nth characters from a string, where N is the count of characters you want to strip. Notice that if you have a list of items with varying locations of the character of interest,
…
Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times
Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times
Here are two formulas showing elapsed time in hours between a Start Time and an End Time.
In the first picture, the formula in cell E1 is =(B2-A2)*24.
You can format that cell as Number and set as many or few decimals as you like.
…
Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only
Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only
Here are formulas using the SUMPRODUCT function that sum a range of cells that reside in only the even-numbered or odd-numbered rows.
Summing only the even-numbered rows.
The formula in selected cell C21 copied to cell F21 is
=SUMPRODUCT(C4:C18,MOD(ROW(C4:C18)+1,2))
Summing only the odd-numbered rows.
…
Tom’s Tutorials For Excel: Verifying a Time as AM or PM
Tom’s Tutorials For Excel: Verifying a Time as AM or PM
Here’s how you can verify a cell’s time value as either morning (AM), or afternoon or evening (PM). This is useful if you encounter a cell whose formatting does not show the time itself, such as in the pictured example below.
…
Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells
Tom’s Tutorials For Excel: Incremental Summing: Every 5 Cells
Here’s how you can incrementally sum a range of cells. In the pictured example, each set of five cells in range D2:D26 are summed in range G2:G6.
The formula in cell G2 and copied down to cell G6 is
=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*5,0,5,1))
You can do this with any incremental count;
…
Tom’s Tutorials For Excel: Extracting the Last Word in a Cell
Tom’s Tutorials For Excel: Extracting the Last Word in a Cell
When you want to extract the last word in a cell, be it a last name or whatever the word or character is that follows the last space in a string, this formula can do that,
…
Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates
Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates
The YEARFRAC function returns a decimalized number as a fraction of year(s) that represents the count of whole days betweeen the start date (“Hire Date” in the pictured example), and the end date (today’s date in cell F1).
…
Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative
Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative
I previously posted this manual example, and this programmed example for converting formula references from relative to absolute. The manual example showed how the row and / or column references can be kept or omitted.
…
Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups
Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups
I previously posted this example of a reverse lookup, using the INDEX and MATCH functions.
The VLOOKUP function is commonly used with a table of data where the item being looked up is in a column to the left of the column holding the corresponding item you want to return,
…
Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error
Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error
I previously posted this example with the SUMIF function for bypassing the #DIV/O! error.
Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function,
…
Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references
Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references
I previously posted this macro example for converting a bunch of formula-containing cells all at once, from relative to absolute references.
Alternatively, you can quickly toggle a single formula’s row and/or column references for relative and absolute with an easy keyboard shortcut.
…
Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets
Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets
Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2,
…
Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell
Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell
Here is an example for returning the first word in a cell, with a formula that can be easily modified to return all contents to the left of the first found specified character in a cell.
…
Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two
Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two
I recently posted two examples of array constants:
how to avoid a lookup table for VLOOKUP, and how to average the 4 highest numbers in a list.
Here’s how you can sum all numbers in a list except for the two lowest or the two highest.
…
Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List
Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List
I recently posted this example of an array constant to avoid a lookup table for VLOOKUP.
Today, an array constant is employed to collect — and then calculate the average of —
…
Tom’s Tutorials For Excel: Calculating the Date for Easter
Tom’s Tutorials For Excel: Calculating the Date for Easter
Easter (often referred to as Easter Sunday) is a Christian festival and holiday. Easter always falls on a Sunday, but its date varies depending on the calendar year and the phase of the moon.
Specifically,
…
Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text
Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text
When you have a mixed list of cells that contain negative numbers, positive numbers, text, and blanks, here is a formula to calculate the average for only the positive numbers.
In the picture,
…
Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number
Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number
Most businesses account their annual operations on a January to December schedule. For those businesses, their quarterly calendar looks like this:
Quarter 1: January, February, March.
Quarter 2: April, May, June.
Quarter 3: July,
…
Tom’s Tutorials For Excel: Formatting a Date With Text
Tom’s Tutorials For Excel: Formatting a Date With Text
Here’s how you can enter a date in a cell, and apply a custom date format to make the cell look as if it contains text. You can use that date in math functions elsewhere, as shown below.
…
Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table
Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table
When using a lookup function such as VLOOKUP, you can avoid a lookup table on a worksheet and insert the lookup elements directly into the formula itself. This is done by using an array constant which is shown by example in the pictured formula
=VLOOKUP(B4,{0,”D”;71,”C-“;72,”C”;76,”C+”;79,”B-“;
…
Tom’s Tutorials For Excel: Flipping a List
Tom’s Tutorials For Excel: Flipping a List
If you want to flip a list so the order of items is vertically reversed, here’s one way to do that using this formula in the pictured example:
=INDEX(A:A,COUNTA(A:A)+1-ROW())
…
Tom’s Tutorials For Excel: Entering a Static Date and Time
Tom’s Tutorials For Excel: Entering a Static Date and Time
As you probably know, you can enter =TODAY() and get the current date, or =NOW() and format the cell as a time to get the current time.
Those functions belong to a category of volatile functions,
…
Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values
Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values
By pressing the Ctrl+~ keys on your keyboard, you can toggle between showing all the worksheet’s formulas or showing only values.
…
Tom’s Tutorials For Excel: Selecting Only Constants or Formulas
Tom’s Tutorials For Excel: Selecting Only Constants or Formulas
Sometimes you want to do something — such as format, delete or edit — all cells containing constants or formulas at the same time. Here’s how to select all such cells so you can thereafter do to them what you want.
…
Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function
Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function
Here’s a tip for showing Excel’s Help window for the function you are in the process of entering.
In the picture, the numbers in column C are wanting to be subtotaled. The SUBTOTAL function has 11 different mathematical argument numbers —
…
Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row
Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row
Sometimes you want options in your VBA arsenal for requests to show results in ad hoc fashions.
In the picture, a table has several columns, each containing a varying count of numeric entries.
…
Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula
Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula
In yesterday’s blog entry, I showed an example of using the Wingdings2 font style with Conditional Formatting to show a checkmark in a cell for completed items. Here’s how you can employ a progress bar using a the REPT function with the Wingdings font style.
…
Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting
Tom’s Tutorials For Excel: Checkmarking a Cell With Standard and Conditional Formatting
Here’s how you can automatically show a checkmark in a cell, using standard formatting for a font type, and Conditional Formatting to identify when the checkmark exists. There is no programming code involved; it’s all native Excel formatting.
…
Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula
Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula
Here’s how you can use Data Validation with the INDIRECT function in a single formula to quickly and easily view the contents of other worksheets in your workbook.
Suppose your workbook is the company budget,
…
Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off
Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off
When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function.
…
Tom’s Tutorials For Excel: Filling Empty Cells With Value From Above
Tom’s Tips For Excel Videos: Filling Empty Cells With Value From Above
Sometimes you get data in a column which has a lot of blank cells that represent whatever text was in the last filled cell above them.
When you need to fill in hundreds or thousands of empty cells,
…
Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing
Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.
With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.
Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.
…
Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns
Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns
Here are a couple of shortcuts to quickly sum the totals for rows and columns in a data table.
The first pair of pictures shows what you have and what you want.
…
Tom’s Tutorials For Excel: Couple of Concatenation Conundrums
Tom’s Tutorials For Excel: Couple of Concatenation Conundrums
Here are two examples of concatenating cells for whacky but not unheard-of situations.
The first example is alternating cells in column being concatenated in successive cells.
The formula in cell B4 and copied down to cell B21 is:
=OFFSET(A1,ROW()-1,)&IF(ISBLANK(OFFSET(A1,ROW()-1,)),””,” “)
&OFFSET(A2,ROW()-1,)
Note that I broke the single-line formula into two lines here,
…
Tom’s Tutorials For Excel: Summing Ranges From Multiple Worksheets
Tom’s Tutorials For Excel: Summing Ranges From Multiple Worksheets
Here’s a visual representation of how to compose a formula on one worksheet that performs a mathematical operation from data on several other worksheets. The numbers in cells J5:J11 on the January, February, and March worksheets are all summed in the Total_Quarter1 worksheet.
…
Tom’s Tutorials For Excel: Returning a Formula’s Arguments
Tom’s Tutorials For Excel: Returning a Formula’s Arguments
I saw a question last year on one of the Excel newsgroups, asking for a way to return a formula’s arguments. I wondered, as you probably are, why this person wanted such a thing.
Still, I was intrigued at the question,
…
Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function
Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function
You can add a text notation to a worksheet formula by using the N function. This is useful when you want to look at a formula and know something about it by reading actual descriptive words.
…
Read more ›
Tags: Analytics, Big Data, Business Intelligence, Comments / Screen Tips, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis